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