Hello
Hopefully someone can shed some light on the following issue. After
chatting at irc.freenode.ne t/#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 7 6077
"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" <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_ty pe_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*******@postg resql.org so that your
message can get through to the mailing list cleanly
"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
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_ty pe_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
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*******@postg resql.org
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_s ections ( <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_s ections ( integer ) RETURNS setof sec_sections
AS '
DECLARE
userid ALIAS for $1;
resulter sec_sections%ro wtype;
BEGIN
IF userid is NULL THEN
RAISE EXCEPTION ''No user provided : ref usr32'';
RETURN 0;
END IF;
PERFORM fn_util07_isadm in(userid);
FOR resulter IN
SELECT
s.n_id as sectionid,
s.t_name as sectionname,
COALESCE(to_cha r(lupd.updated, ''DD Mon''), ''None'') as secupdated,
COALESCE(rept.n um,0) as secreports,
COALESCE(usr.nu m,0) as secusers
...etc..
--
Rory Campbell-Lange
<ro**@campbel l-lange.net>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
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_func tion ( $1 );
total := use_curs_to_do_ totaling_functi on ( ref_cursors );
RETURN total;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION get_ref_cursor_ from_other_func tion ( 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_functi on ( 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.<somecolv al>;
END LOOP;
RETURN (total);
END;
' LANGUAGE 'plpgsql';
--
Rory Campbell-Lange
<ro**@campbel l-lange.net>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mike |
last post by:
mylist =
I want to print out the names of the variables in mylist (not the
values of a, b, and c). How do I go about doing this. Thanks.
Mike
|
by: Gregory Piñero |
last post by:
Hey guys, would someone mind giving me a quick rundown of how
references work in Python when passing arguments into functions? The
code below should highlight my specific confusion:
<code>
bool1=True
lst1=
def func1(arg1): arg1.append(4)
|
by: Andy |
last post by:
Hi,
I'm doing a nested pass by reference of a vector. I'm in Vis Studio 2003.
Example:
vector <unsigned long long> vect;
func1(vect);
where func1 calls func2(vect);
|
by: george doubleu |
last post by:
hi,
i'm using "gcc version 3.3.3 (Debian)" to compile the program pasted below.
I get the two warnings you can see in the remarks. The second warning is
perfectly OK for me, but the first one I don't understand.
Isn't the "const int *" construct in the signature of func1 a hint for the
user, that func1 doesn't modify **arg? Why then is it dangerous to
pass an alterable argmument?
|
by: brian_harris |
last post by:
I have a function that passes a string class pointer to a function, this
function is then suppose to fill it and the outer function uses it. But I
believe that I am running into problem due to manged memory. Because it has
value in inner function, but does not have the assigned value in outher
function. So I need to know how to declare this variable and make
assignments correctly so that it will have value when it is back in outher...
| |
by: NevilleDNZ |
last post by:
Can anyone explain why "begin B: 123" prints, but 456 doesn't?
$ /usr/bin/python2.3 x1x2.py
begin A:
Pre B: 123 456
begin B: 123
Traceback (most recent call last):
File "x1x2.py", line 13, in ?
A()
File "x1x2.py", line 11, in A
|
by: tai |
last post by:
Hi.
I'm looking for a way to define a function that's only effective inside
specified function.
Featurewise, here's what I want to do:
bar_plugin_func = function() { ...; setTimeout(...); ... };
wrap_func(bar_plugin_func);
bar_plugin_func(); // calls custom "setTimeout"
|
by: CptDondo |
last post by:
How do you declare a function with 0 or mroe arguments?
I have a bunch of functions like this:
void tc_cm(int row, int col);
void tc_do(void);
void tc_DO(int ln);
and I am trying to declare a pointer to them:
|
by: Guillermo |
last post by:
Hi,
This must be very basic, but how'd you pass the same *args several
levels deep?
def func2(*args)
print args # ((1, 2, 3),)
# i want this to output (1, 2, 3) as func1!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |