From: Joe Conway <mail@joeconway.com>
To: Ian Morgan <imorgan@webcon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT in a function != SELECT ?
Date: Wed, 24 Apr 2002 23:13:37 -0700
Message-ID: <3CC79E91.5070903@joeconway.com> (raw)
References: <Pine.LNX.4.44.0204250101300.26231-100000@light.webcon.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, imorgan@webcon.net
Subject: Re: SELECT in a function != SELECT ?
In-Reply-To: <3CC79E91.5070903@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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