pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT in a function != SELECT ?
4+ messages / 4 participants
[nested] [flat]

* SELECT in a function != SELECT ?
@ 2002-04-25 05:44 Ian Morgan <imorgan@webcon.net>
  2002-04-25 06:13 ` Re: SELECT in a function != SELECT ? Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Ian Morgan @ 2002-04-25 05:44 UTC (permalink / raw)
  To: pgsql-sql

While trying to formulate an answer to another user's query on this list, I
came a across what I think is strange behaviour in an SQL funsction:

The 3rd column of my 'contacts' table is 'first_name'.

A regular select will allow me to find this:

SELECT a.attname FROM pg_class c, pg_attribute a
        WHERE c.relname = 'contacts'
        and a.attnum = 3 and a.attrelid=c.oid;

  attname
------------
 first_name
(1 row)


But when turned into a function:

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)


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?

Regards,
Ian Morgan
-- 
-------------------------------------------------------------------
 Ian E. Morgan        Vice President & C.O.O.         Webcon, Inc.
 imorgan@webcon.net         PGP: #2DA40D07          www.webcon.net
    *  Customized Linux network solutions for your business  *
-------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SELECT in a function != SELECT ?
  2002-04-25 05:44 SELECT in a function != SELECT ? Ian Morgan <imorgan@webcon.net>
@ 2002-04-25 06:13 ` Joe Conway <mail@joeconway.com>
  2002-04-25 13:53   ` Re: SELECT in a function != SELECT ? Jan Wieck <janwieck@yahoo.com>
  2002-04-25 16:46   ` Re: SELECT in a function != SELECT ? David Stanaway <david@stanaway.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Joe Conway @ 2002-04-25 06:13 UTC (permalink / raw)
  To: Ian Morgan <imorgan@webcon.net>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SELECT in a function != SELECT ?
  2002-04-25 05:44 SELECT in a function != SELECT ? Ian Morgan <imorgan@webcon.net>
  2002-04-25 06:13 ` Re: SELECT in a function != SELECT ? Joe Conway <mail@joeconway.com>
@ 2002-04-25 13:53   ` Jan Wieck <janwieck@yahoo.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jan Wieck @ 2002-04-25 13:53 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Ian Morgan <imorgan@webcon.net>; pgsql-sql

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 #





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SELECT in a function != SELECT ?
  2002-04-25 05:44 SELECT in a function != SELECT ? Ian Morgan <imorgan@webcon.net>
  2002-04-25 06:13 ` Re: SELECT in a function != SELECT ? Joe Conway <mail@joeconway.com>
@ 2002-04-25 16:46   ` David Stanaway <david@stanaway.net>
  1 sibling, 0 replies; 4+ messages in thread

From: David Stanaway @ 2002-04-25 16:46 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Ian Morgan <imorgan@webcon.net>; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-04-25 16:46 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-25 05:44 SELECT in a function != SELECT ? Ian Morgan <imorgan@webcon.net>
2002-04-25 06:13 ` Joe Conway <mail@joeconway.com>
2002-04-25 13:53   ` Jan Wieck <janwieck@yahoo.com>
2002-04-25 16:46   ` David Stanaway <david@stanaway.net>

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