Re: SELECT in a function != SELECT ?

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