Postgres Pro
Downloads
Home   >   mailing lists

Thread: Pl/pgSQL Question.

Pl/pgSQL Question.

From
Bruno Ricardo Sacco
Date:
Im traing to make a pl/pgsql function that display records of a given table.
I pass the name of the table has an argument for the function.

Using the,
FOR myRec IN SELECT xxxxxxxxxxx LOOP

do some of the work, but I still need to know the names of the fields.

for example:    myRec.name

there is a way to do same thing like :
    myRec[0]

to reference fields in the record ?

I can retriebe the names of the table fields from then system tables, but
that din't help me.

Anyome knows how ?

Sorry for me english.....


-------------------------------------------------------------------------------------------------------------------------------
Bruno Ricardo Sacco
IT Manager
HyperNET ISP

SELECT in a function != SELECT ?

From
Ian Morgan
Date:
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
foryour business  *
 
-------------------------------------------------------------------

Re: SELECT in a function != SELECT ?

From
Joe Conway
Date:
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

Re: SELECT in a function != SELECT ?

From
Jan Wieck
Date:
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 #

Re: SELECT in a function != SELECT ?

From
David Stanaway
Date:
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