Обсуждение: Passing RECORD variable from func1() to func2()
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
"Henry Combrinck" <henry@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
> "Henry Combrinck" <henry@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
"Henry Combrinck" <henry@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
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)
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
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 (henry@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
<rory@campbell-lange.net>
<www.campbell-lange.net>
Howzit Henry
On 06/09/04, Henry Combrinck (henry@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
<rory@campbell-lange.net>
<www.campbell-lange.net>