Postgres Pro
Downloads
Home   >   mailing lists

Thread: Passing a row

Passing a row

From
Terry Lee Tucker
Date:
PostgreSQL Version:
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)

I'm trying to pass a row into a function and cannot get it to work. The code
doing the calling is (logs_preupd_func):
DECLARE
    test    RECORD;
...
...

    SELECT INTO test * FROM logs WHERE order_num = new.order_num;
    PERFORM valid_event_time (test);

The defintition for valid_event_time ():

CREATE OR REPLACE FUNCTION valid_event_time (logs)
    RETURNS text AS '
.
.
.
I get the following error:
WARNING:  Error occurred while executing PL/pgSQL function logs_preupd_func
WARNING:  line 131 at assignment
ERROR:  Attribute "test" not found

This example in the docs indicates that this can be done as in:
CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS '
DECLARE
    in_t ALIAS FOR $1;
    use_t table2name%ROWTYPE;
BEGIN
    SELECT * INTO use_t FROM table2name WHERE ... ;
    RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
END;
' LANGUAGE 'plpgsql';

Can anybody show me the error of my ways?

Thanks...
--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: Passing a row

From
Tom Lane
Date:
Terry Lee Tucker <terry@esc1.com> writes:
> Can anybody show me the error of my ways?

Seems like I just answered this yesterday ... ah, here we go:
http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php

I don't recall if 7.3 has all the functionality of 7.4 in this regard.

            regards, tom lane

Re: Passing a row

From
Terry Lee Tucker
Date:
Thanks for your response. I looked the posts you reference, but, to be quite
frank, I didn't understand. I am confused regarding the sample code in the
documentation I am looking at. Here it is:

CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
DECLARE
    in_t ALIAS FOR $1;
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
' LANGUAGE 'plpgsql';

If this code is workable code, I would love to see an example of the code that
would call such a function so I can determine how to pass the parameter. I
think I did a poor job of expressing my ignorance ;o)

Thanks...

On Friday 26 March 2004 10:59 am, Tom Lane saith:
> Terry Lee Tucker <terry@esc1.com> writes:
> > Can anybody show me the error of my ways?
>
> Seems like I just answered this yesterday ... ah, here we go:
> http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php
>
> I don't recall if 7.3 has all the functionality of 7.4 in this regard.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Quote: 53
"A soul can never attain the knowledge of God unless God Himself in His
 condescension takes hold of it and raises it up to Himself. For the
 human intellect lacks the power to ascend and to participate in divine
 illumination, unless God Himself draws it up -- in so far as this is
 possible for the human intellect -- and illumines it with rays of divine
 light."

 --The Philokalia

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: Passing a row

From
Terry Lee Tucker
Date:
Ok. Now I get it. Guess I worked on this too long yesterday. Thanks for the
help.

On Friday 26 March 2004 04:46 pm, Terry Lee Tucker saith:
> Thanks for your response. I looked the posts you reference, but, to be
> quite frank, I didn't understand. I am confused regarding the sample code
> in the documentation I am looking at. Here it is:
>
> CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
> DECLARE
>     in_t ALIAS FOR $1;
> BEGIN
>     RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
> END;
> ' LANGUAGE 'plpgsql';
>
> If this code is workable code, I would love to see an example of the code
> that would call such a function so I can determine how to pass the
> parameter. I think I did a poor job of expressing my ignorance ;o)
>
> Thanks...
>
> On Friday 26 March 2004 10:59 am, Tom Lane saith:
> > Terry Lee Tucker <terry@esc1.com> writes:
> > > Can anybody show me the error of my ways?
> >
> > Seems like I just answered this yesterday ... ah, here we go:
> > http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php
> >
> > I don't recall if 7.3 has all the functionality of 7.4 in this regard.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com