Thread: Bug in my ( newbie ) mind?

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
<<--

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

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

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

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