Received: from joeconway.com (unknown [63.210.180.150]) by postgresql.org (Postfix) with ESMTP id 3FFF2474E6D for ; Thu, 25 Apr 2002 02:13:28 -0400 (EDT) Received: from [192.168.5.2] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 3.5.6) with ESMTP-TLS id 343597; Wed, 24 Apr 2002 22:07:51 -0700 Message-ID: <3CC79E91.5070903@joeconway.com> Date: Wed, 24 Apr 2002 23:13:37 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.9) Gecko/20020311 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ian Morgan Cc: pgsql-sql@postgresql.org Subject: Re: SELECT in a function != SELECT ? References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/293 X-Sequence-Number: 7557 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