Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (Postfix) with SMTP id BD401475E11 for ; Thu, 25 Apr 2002 09:59:07 -0400 (EDT) Received: from psc.progress.com (HELO saturn.janwieck.net) (janwieck@192.233.92.200 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 25 Apr 2002 13:59:11 -0000 Received: (from wieck@localhost) by saturn.janwieck.net (8.11.2/8.11.2) id g3PDrLd24680; Thu, 25 Apr 2002 09:53:21 -0400 From: Jan Wieck Message-Id: <200204251353.g3PDrLd24680@saturn.janwieck.net> Subject: Re: SELECT in a function != SELECT ? In-Reply-To: <3CC79E91.5070903@joeconway.com> from Joe Conway at "Apr 24, 2002 11:13:37 pm" To: Joe Conway Date: Thu, 25 Apr 2002 09:53:19 -0400 (EDT) Cc: Ian Morgan , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/304 X-Sequence-Number: 7568 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. Not because it's known to be a name, but because enclosing it into quotes makes it the literal string '$1' instead of the parameter passed in. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #