Re: SELECT in a function != SELECT ?

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