pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Ian Morgan <imorgan@webcon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT in a function != SELECT ?
Date: Wed, 24 Apr 2002 23:13:37 -0700
Message-ID: <3CC79E91.5070903@joeconway.com> (raw)
References: <Pine.LNX.4.44.0204250101300.26231-100000@light.webcon.net>

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





view thread (4+ messages)  latest in thread

Message-ID: <3CC79E91.5070903@joeconway.com>
Permalink:  ../3CC79E91.5070903@joeconway.com/
Also on:    postgresql.org/message-id/3CC79E91.5070903@joeconway.com

 · 

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: mail@joeconway.com, imorgan@webcon.net
  Subject: Re: SELECT in a function != SELECT ?
  In-Reply-To: <3CC79E91.5070903@joeconway.com>

* 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