pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Stanaway <david@stanaway.net>
To: Joe Conway <mail@joeconway.com>
Cc: Ian Morgan <imorgan@webcon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT in a function != SELECT ?
Date: 25 Apr 2002 11:46:31 -0500
Message-ID: <1019753191.13890.12.camel@ciderbox> (raw)
In-Reply-To: <3CC79E91.5070903@joeconway.com>
References: <Pine.LNX.4.44.0204250101300.26231-100000@light.webcon.net>
	<3CC79E91.5070903@joeconway.com>

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

Attachments:

  signature.asc (application/pgp-signature, 523B) download

view thread (4+ messages)

Message-ID: <1019753191.13890.12.camel@ciderbox>
Permalink:  ../1019753191.13890.12.camel@ciderbox/
Also on:    postgresql.org/message-id/1019753191.13890.12.camel@ciderbox

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: david@stanaway.net, mail@joeconway.com, imorgan@webcon.net
  Subject: Re: SELECT in a function != SELECT ?
  In-Reply-To: <1019753191.13890.12.camel@ciderbox>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox