Thread

1. Bug in my ( newbie ) mind?

   Christopher Sawtell <csawtell@xtra.co.nz> — 2001-02-21T05:00:13Z

   Greetings,
   
     Please, what am I doing wrong?
   
   chris=# \d phone_prefix
   Table "phone_prefix"
   -[ RECORD 1 ]----------------------------------------------------------
   Attribute | number
   Type      | integer
   Modifier  | not null default nextval('"phone_prefix_number_seq"'::text)
   -[ RECORD 2 ]----------------------------------------------------------
   Attribute | prefix
   Type      | text
   Modifier  |
    
   Index: phone_prefix_number_key 
   
   chris=#  select phone_prefix.prefix order by random() limit 1;
    prefix
   --------
    384
   (1 row)
   
   Wonderful, works exactly as expected.
   
   chris=# select  lpad((random()*10000)::int, 4, '0')::text as "Number";
    Number
   --------
    2958
   (1 row)
   
   ditto
   
   But attempting to concatenate the two is a disaster.
   
   chris=# select phone_prefix.prefix order by random() limit 1 || '-' || 
   lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
   ERROR:  parser: parse error at or near "||"
   chris=# 
   
   What am i doing wrong?
   
   chris=# select version();
                                   version
   ------------------------------------------------------------------------
    PostgreSQL 7.1beta3 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66
   (1 row)                                                                    
                                                   
   Thanks a 10^6
   
   p.s. imho it would be a terrific help, especially for new-comers to SQL 
   like me, if the parser could be persuaded to utter just a tiny glimmer of 
   a hint as to what it thinks one's mistake is instead of the rather 
   enigmatic "ERROR:  parser: parse error at or near".
   Is it possible for mere mortals to help?
   
   -- 
   Sincerely etc.,
   
    NAME       Christopher Sawtell
    CELL PHONE 021 257 4451
    ICQ UIN    45863470
    EMAIL      csawtell @ xtra . co . nz
    CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
   
    -->> Please refrain from using HTML or WORD attachments in e-mails to me 
   <<--
   
   
   

2. Re: Bug in my ( newbie ) mind?

   Tod McQuillin <devin@spamcop.net> — 2001-02-21T05:34:31Z

   On Wed, 21 Feb 2001, Christopher Sawtell wrote:
   
   > chris=# select phone_prefix.prefix order by random() limit 1 || '-' ||
   > lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
   
   All the things you are selecting need to come in the first part of the
   query.
   
   like,
   
   SELECT prefix || '-' || lpad((random()*10000)::int, 4, '0')::text as
   "Phone Number" from phone_prefix order by random() limit 1;
   -- 
   Tod McQuillin
   
   
   
   

3. Bug in my ( newbie ) mind?

   Dan Lyke <danlyke@flutterby.com> — 2001-02-21T06:03:39Z

   Christopher Sawtell writes:
   > chris=# select phone_prefix.prefix order by random() limit 1 || '-' || 
   > lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
   > ERROR:  parser: parse error at or near "||"
   
   This sure won't fix everything, but at the very least you need to
   parenthesize that first select clause inside another select. For
   instance, this works:
   
      select (select '123'::text) || (select '456'::text);
   
   So one might think that, with appropriate casting, something more
   like:
   
      select (select phone_prefix.prefix order by random() limit 1) || ...
   
   would be more likely to work (modulo some casting and such).
   
   Dan
   
   
   

4. Re: Bug in my ( newbie ) mind?

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-21T06:33:01Z

   Dan Lyke <danlyke@flutterby.com> writes:
   > So one might think that, with appropriate casting, something more
   > like:
   >    select (select phone_prefix.prefix order by random() limit 1) || ...
   > would be more likely to work (modulo some casting and such).
   
   Note this will not work in pre-7.1 releases --- 7.1 is the first that
   allows ORDER BY and LIMIT clauses in a sub-select.
   
   			regards, tom lane
   
   

5. Re: Bug in my ( newbie ) mind?

   Dan Lyke <danlyke@flutterby.com> — 2001-02-21T16:11:33Z

   Tom Lane writes:
   > Note this will not work in pre-7.1 releases --- 7.1 is the first
   > that allows ORDER BY and LIMIT clauses in a sub-select.
   
   Yah, the way I figure it is that if you're a PostgreSQL user, the very
   least you can do for the community is be running the latest beta on
   your development machines so that you can help find the bugs.
   
   Open source software doesn't really cost any less than commercial
   software, it just costs in different less tangible ways. And it's
   those that make the quality higher.
   
   Dan