Received: from ciderbox.stanaway.net (ciderbox.stanaway.net [170.76.50.98]) by postgresql.org (Postfix) with ESMTP id BA451475BB6 for ; Thu, 25 Apr 2002 12:46:42 -0400 (EDT) Received: by ciderbox.stanaway.net (Postfix, from userid 1000) id A356F9358C; Thu, 25 Apr 2002 11:46:31 -0500 (CDT) Subject: Re: SELECT in a function != SELECT ? From: David Stanaway To: Joe Conway Cc: Ian Morgan , pgsql-sql@postgresql.org In-Reply-To: <3CC79E91.5070903@joeconway.com> References: <3CC79E91.5070903@joeconway.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-0eGb4G6Jc6GkW0zm87il" X-Mailer: Ximian Evolution 1.0.3 Date: 25 Apr 2002 11:46:31 -0500 Message-Id: <1019753191.13890.12.camel@ciderbox> Mime-Version: 1.0 X-Archive-Number: 200204/310 X-Sequence-Number: 7574 --=-0eGb4G6Jc6GkW0zm87il Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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 =3D ''$1'' > > and a.attnum =3D $2 and a.attrelid=3Dc.oid > > ' > > LANGUAGE SQL; > >=20 > > SELECT get_colname('contacts',3); > >=20 > > get_colname > > ------------- > >=20 > > (1 row) > >=20 >=20 > Try this: >=20 > test=3D# CREATE FUNCTION get_colname (name,smallint) RETURNS name AS=20 > 'SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname =3D $1= =20 > and a.attnum =3D $2 and a.attrelid=3Dc.oid' LANGUAGE SQL; > CREATE > test=3D# SELECT get_colname('foo',3); > get_colname > ------------- > f3 > (1 row) >=20 > The $1 is already known to be a name, so you don't want the '' around it= =20 > in the function definition. >=20 > >=20 > > The result is empty!? > >=20 > > Even more strange: > >=20 > > SELECT 'x'||get_colname('contacts',3)||'x' as foo; > >=20 > > foo > > ----- > >=20 > > (1 row) > >=20 > > 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? > >=20 >=20 > No, this is correct. The function is returning NULL, and anything=20 > 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 --=-0eGb4G6Jc6GkW0zm87il Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iQEXAwUAPMgy576yIDFGeSAyFAK0DAQAi8gHn+dRVCrSXScD2D8jMcfb7Nesy2Vu GWq0k14ZVlXUSuj/i2GLuq515sEzwcZCFNF2cuMOtnFN+CIiwHadvfwm0i+SCjnz xXzar3O+6vMx1HlAiB4mno2K7v8bJ83gL0rwmMY/kSYY2Hq6pwu0LTm2y38IJ6Kj vIqnTz40SqMD/RoLpz/76r9YFMEkLI8C1aZbBDt6z1+W2HstFjAW+iZ1yFl4LvJk 6eiPetUCZuZTD3UE4jFd5fhydNI0/Lu1Rau9YxUhoC+tv5eEZKkPvrXDSLyIEwHD KYU4IUFlOwzU8OL4iKj8pQcGeVIcT/y0wODXkNOmLkkNQmUpRXCzYzY/ =6+N+ -----END PGP SIGNATURE----- --=-0eGb4G6Jc6GkW0zm87il--