Passing RECORD variable from func1() to func2()

[Henry Combrinck]

Hello

Hopefully someone can shed some light on the following issue. After
chatting at irc.freenode.net/#postgresql, without success, this is my last
effort before giving up and using a temp table.

Essentially, I would like to pass a RECORD variable from one function to
another using plpgsql:

func2(record)
rec1 alias for $1
begin
-- do work on rec1.*
raise notice ''val1=% val2=%'', rec1.col1, rec1.col2;
end;

func1()
declare
temprec record;
begin
for temprec in select * from table1, table2...
loop
...
select func2(temprec); /* pass temprec row to func2() */
...
end loop;
end;

Then call with:
SELECT FUNC1();

Is this possible? The docs only speak about RECORD type being used to
*return* rows, but not to pass it.

Any pointers would be appreciated.

Regards
Henry
--------------------------------------------------------
This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus,
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now! 086 11 11 440

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Henry Combrinck" <he***@metroweb.co.za> writes:

Essentially, I would like to pass a RECORD variable from one function to
another using plpgsql: func2(record)

You can't declare a plpgsql function that accepts RECORD; this is simply
not supportable. (For one thing, which actual record types should such
a function be considered to match? It's a nonstarter even at the level
of function argument resolution, let alone the implementation issues.)
It has to take some named rowtype, instead.

There are implementation restrictions in 7.4 and before that prevent
plpgsql functions from passing row or record variables to other
functions, so you'd have problems at the calling end as well.
FWIW these restrictions are fixed for 8.0.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Henry Combrinck]

> "Henry Combrinck" <he***@metroweb.co.za> writes:

Essentially, I would like to pass a RECORD variable from one function to
another using plpgsql:

func2(record)

You can't declare a plpgsql function that accepts RECORD; this is simply
not supportable. (For one thing, which actual record types should such
a function be considered to match? It's a nonstarter even at the level
of function argument resolution, let alone the implementation issues.)
It has to take some named rowtype, instead.

Thanks for the response.

Can you give an example of what a "named rowtype" is? Are you refering to
creating some kind of custom TYPE, and then passing that to
func2(custom_type_row)?

Thanks
Henry
--------------------------------------------------------
This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus,
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now! 086 11 11 440

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Henry Combrinck" <he***@metroweb.co.za> writes:

Can you give an example of what a "named rowtype" is?

The result of CREATE TYPE AS, or the row type implicitly created for a
table. For instance

CREATE TYPE complex AS (r float, i float);

CREATE FUNCTION abs(complex) RETURNS float AS ...

or

CREATE TABLE users (name text, ...);

CREATE FUNCTION foobar(users) RETURNS ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Alvaro Herrera]

On Mon, Sep 06, 2004 at 10:39:54PM +0200, Henry Combrinck wrote:

Can you give an example of what a "named rowtype" is? Are you refering to
creating some kind of custom TYPE, and then passing that to
func2(custom_type_row)?

You can use a table's rowtype, that is, a type that has the name of the
table and the same column types. Or you can create a "standalone type" with

CREATE TYPE foo AS (f1 int, f2 text, ...)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Henry Combrinck]

Thanks for the CREATE TYPE samples. *Using* the custom types seems to be
a problem (or rather, I'm using it incorrectly). The following code fails
with the error message

"WARNING: Error occurred while executing PL/pgSQL function f_test00
WARNING: line 8 at SQL statement
ERROR: Attribute "rec1" not found"

/* create same columns as table1 */
create type TYPE_T1 as (col1 int, col2 int,... etc);
CREATE or replace FUNCTION f_test01 (TYPE_T1)
RETURNS int AS '
declare
temprec alias for $1
begin
raise notice ''eid: % pcyc: %'', temprec.col1, temprec.col2;
return 0;
end;
' LANGUAGE 'plpgsql';

CREATE or replace FUNCTION f_test00 () RETURNS int AS '
declare
rec1 TYPE_T1%ROWTYPE;
begin
for rec1 in select * from table1
loop
select f_test01(rec1); /* this is where it fails. */
end loop;
return 0;
end;
' LANGUAGE 'plpgsql';

--------------------------------------------------------
This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus,
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now! 086 11 11 440

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Rory Campbell-Lange]

howzit Henry

I've pasted in the head of one of my functions. Hope it helps. It is
called through the following:

SELECT * from fn_usr32_show_sections ( <integer> );

Rory

On 07/09/04, Henry Combrinck (he***@metroweb.co.za) wrote:

Thanks for the CREATE TYPE samples. *Using* the custom types seems to be
a problem (or rather, I'm using it incorrectly). The following code fails
with the error message

"WARNING: Error occurred while executing PL/pgSQL function f_test00
WARNING: line 8 at SQL statement
ERROR: Attribute "rec1" not found"

/* create same columns as table1 */
create type TYPE_T1 as (col1 int, col2 int,... etc); .... declare
rec1 TYPE_T1%ROWTYPE;
begin
for rec1 in select * from table1
loop
select f_test01(rec1); /* this is where it fails. */
end loop;
return 0;
end;
' LANGUAGE 'plpgsql';

CREATE TYPE sec_sections as (
sectionid INTEGER,
sectionname VARCHAR,
secupdated VARCHAR,
secreports INTEGER,
secusers INTEGER
);

CREATE OR REPLACE FUNCTION
fn_usr32_show_sections ( integer ) RETURNS setof sec_sections
AS '
DECLARE
userid ALIAS for $1;
resulter sec_sections%rowtype;
BEGIN

IF userid is NULL THEN
RAISE EXCEPTION ''No user provided : ref usr32'';
RETURN 0;
END IF;

PERFORM fn_util07_isadmin(userid);

FOR resulter IN
SELECT
s.n_id as sectionid,
s.t_name as sectionname,
COALESCE(to_char(lupd.updated, ''DD Mon''), ''None'') as secupdated,
COALESCE(rept.num,0) as secreports,
COALESCE(usr.num,0) as secusers
...etc..
--
Rory Campbell-Lange
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Rory Campbell-Lange]

Howzit Henry

On 06/09/04, Henry Combrinck (he***@metroweb.co.za) wrote:

Essentially, I would like to pass a RECORD variable from one function to
another using plpgsql:

You may want to have a look at using cursor references.

For instance:

CREATE FUNCTION use_cursors ( INTEGER ) RETURNS INTEGER AS '
DECLARE
ref_cursors REFCURSOR;
total INTEGER := 0;
BEGIN
curs := get_ref_cursor_from_other_function ( $1 );
total := use_curs_to_do_totaling_function ( ref_cursors );
RETURN total;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION get_ref_cursor_from_other_function ( INTEGER ) RETURNS REFCURSOR AS '
DECLARE
next_val REFCURSOR;
BEGIN
OPEN next_val FOR
SELECT * FROM mytable WHERE intcol = $1;
RETURN ( next_val);
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION use_curs_to_do_totaling_function ( REFCURSOR ) RETURNS INTEGER AS '
DECLARE
myrow mytable%rowtype;
total INTEGER := 0;
next_val ALIAS for $1;
BEGIN
LOOP
FETCH next_val INTO myrow;
EXIT WHEN NOT FOUND;
total := total + myrow.<somecolval>;
END LOOP;
RETURN (total);
END;
' LANGUAGE 'plpgsql';

--
Rory Campbell-Lange
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org