pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT in a function != SELECT ?
4+ messages / 4 participants
[nested] [flat]
* SELECT in a function != SELECT ?
@ 2002-04-25 05:44 Ian Morgan <imorgan@webcon.net>
0 siblings, 1 reply; 4+ messages in thread
From: Ian Morgan @ 2002-04-25 05:44 UTC (permalink / raw)
To: pgsql-sql
While trying to formulate an answer to another user's query on this list, I
came a across what I think is strange behaviour in an SQL funsction:
The 3rd column of my 'contacts' table is 'first_name'.
A regular select will allow me to find this:
SELECT a.attname FROM pg_class c, pg_attribute a
WHERE c.relname = 'contacts'
and a.attnum = 3 and a.attrelid=c.oid;
attname
------------
first_name
(1 row)
But when turned into a function:
CREATE FUNCTION get_colname (name,smallint)
RETURNS name AS '
SELECT a.attname FROM pg_class c, pg_attribute a
WHERE c.relname = ''$1''
and a.attnum = $2 and a.attrelid=c.oid
'
LANGUAGE SQL;
SELECT get_colname('contacts',3);
get_colname
-------------
(1 row)
The result is empty!?
Even more strange:
SELECT 'x'||get_colname('contacts',3)||'x' as foo;
foo
-----
(1 row)
Should I not be seeing "xx" as the result here!? What's going on? Anyone
wknow why the above function get_colname isn't doing what I expect?
Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT in a function != SELECT ?
@ 2002-04-25 06:13 Joe Conway <mail@joeconway.com>
parent: Ian Morgan <imorgan@webcon.net>
0 siblings, 2 replies; 4+ messages in thread
From: Joe Conway @ 2002-04-25 06:13 UTC (permalink / raw)
To: Ian Morgan <imorgan@webcon.net>; +Cc: pgsql-sql
Ian Morgan wrote:
> CREATE FUNCTION get_colname (name,smallint)
> RETURNS name AS '
> SELECT a.attname FROM pg_class c, pg_attribute a
> WHERE c.relname = ''$1''
> and a.attnum = $2 and a.attrelid=c.oid
> '
> LANGUAGE SQL;
>
> SELECT get_colname('contacts',3);
>
> get_colname
> -------------
>
> (1 row)
>
Try this:
test=# CREATE FUNCTION get_colname (name,smallint) RETURNS name AS
'SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = $1
and a.attnum = $2 and a.attrelid=c.oid' LANGUAGE SQL;
CREATE
test=# SELECT get_colname('foo',3);
get_colname
-------------
f3
(1 row)
The $1 is already known to be a name, so you don't want the '' around it
in the function definition.
>
> The result is empty!?
>
> Even more strange:
>
> SELECT 'x'||get_colname('contacts',3)||'x' as foo;
>
> foo
> -----
>
> (1 row)
>
> Should I not be seeing "xx" as the result here!? What's going on? Anyone
> wknow why the above function get_colname isn't doing what I expect?
>
No, this is correct. The function is returning NULL, and anything
concatenated with NULL is still NULL.
HTH,
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT in a function != SELECT ?
@ 2002-04-25 13:53 Jan Wieck <janwieck@yahoo.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2002-04-25 13:53 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Ian Morgan <imorgan@webcon.net>; pgsql-sql
Joe Conway wrote:
> Ian Morgan wrote:
> > CREATE FUNCTION get_colname (name,smallint)
> > RETURNS name AS '
> > SELECT a.attname FROM pg_class c, pg_attribute a
> > WHERE c.relname = ''$1''
> > and a.attnum = $2 and a.attrelid=c.oid
> > '
> > LANGUAGE SQL;
> >
> > SELECT get_colname('contacts',3);
> >
> > get_colname
> > -------------
> >
> > (1 row)
> >
>
> Try this:
>
> test=# CREATE FUNCTION get_colname (name,smallint) RETURNS name AS
> 'SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = $1
> and a.attnum = $2 and a.attrelid=c.oid' LANGUAGE SQL;
> CREATE
> test=# SELECT get_colname('foo',3);
> get_colname
> -------------
> f3
> (1 row)
>
> The $1 is already known to be a name, so you don't want the '' around it
> in the function definition.
Not because it's known to be a name, but because enclosing it
into quotes makes it the literal string '$1' instead of the
parameter passed in.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT in a function != SELECT ?
@ 2002-04-25 16:46 David Stanaway <david@stanaway.net>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 4+ messages in thread
From: David Stanaway @ 2002-04-25 16:46 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Ian Morgan <imorgan@webcon.net>; pgsql-sql
On Thu, 2002-04-25 at 01:13, Joe Conway wrote:
> Ian Morgan wrote:
> > CREATE FUNCTION get_colname (name,smallint)
> > RETURNS name AS '
> > SELECT a.attname FROM pg_class c, pg_attribute a
> > WHERE c.relname = ''$1''
> > and a.attnum = $2 and a.attrelid=c.oid
> > '
> > LANGUAGE SQL;
> >
> > SELECT get_colname('contacts',3);
> >
> > get_colname
> > -------------
> >
> > (1 row)
> >
>
> Try this:
>
> test=# CREATE FUNCTION get_colname (name,smallint) RETURNS name AS
> 'SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = $1
> and a.attnum = $2 and a.attrelid=c.oid' LANGUAGE SQL;
> CREATE
> test=# SELECT get_colname('foo',3);
> get_colname
> -------------
> f3
> (1 row)
>
> The $1 is already known to be a name, so you don't want the '' around it
> in the function definition.
>
> >
> > The result is empty!?
> >
> > Even more strange:
> >
> > SELECT 'x'||get_colname('contacts',3)||'x' as foo;
> >
> > foo
> > -----
> >
> > (1 row)
> >
> > Should I not be seeing "xx" as the result here!? What's going on? Anyone
> > wknow why the above function get_colname isn't doing what I expect?
> >
>
> No, this is correct. The function is returning NULL, and anything
> concatenated with NULL is still NULL.
Also name and smallint I don't think are valid types for the params.
Postgres functions don't have named arguments as far as I have seen.
--
David Stanaway
Attachments:
signature.asc (application/pgp-signature, 523B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-04-25 16:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-25 05:44 SELECT in a function != SELECT ? Ian Morgan <imorgan@webcon.net>
2002-04-25 06:13 ` Joe Conway <mail@joeconway.com>
2002-04-25 13:53 ` Jan Wieck <janwieck@yahoo.com>
2002-04-25 16:46 ` David Stanaway <david@stanaway.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox