473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

PLpgSQL-Problem

Hi,

i am playing around with PLpgSQL and can not solve one problem: I am fetching some rows of a special rowtype and wanna give this
rows step by step to a function with this rowtype as parameter.

evertime i get the errormessage: ERROR: Attribut »_row« not found

but the Attribut exists and has values in his fields. i have absolutly no idea was happens there. can someone help me please?

thanx
thomas

-------snip function 1

CREATE OR REPLACE FUNCTION "public"."compress_main" (date) RETURNS boolean AS'
DECLARE
_day ALIAS FOR $1;
_row public.tmp_order_data%ROWTYPE;
_result boolean;
eb bigint;
BEGIN

DELETE FROM hlp_operator WHERE selling_date = _day;

FOR _row IN
SELECT * FROM tmp_order_data WHERE business_day = _day ORDER BY transactiontime
LOOP
IF _row.id_transactiontype = 100 THEN
SELECT INTO _result compress_100(_row);
END IF;
END LOOP;

RETURN TRUE;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-------snip function 2:

CREATE OR REPLACE FUNCTION "public"."compress_100" ("public"."tmp_order_data") RETURNS boolean AS'
DECLARE
_record ALIAS FOR $1;
_shift int8;
BEGIN

/* any code */

RETURN TRUE;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
5 1592
"Thomas Chille" <th****@chille.de> writes:
evertime i get the errormessage: ERROR: Attribut »_row« not found
but the Attribut exists and has values in his fields. i have absolutly no idea was happens there. can someone help me please?
You're out of luck :-( ... plpgsql doesn't presently have the ability to
pass a whole-row value to the main executor, which means this won't
work:
SELECT INTO _result compress_100(_row);


Possibly it will work in 7.5; I've made some preliminary steps in that
direction but it's not all there yet.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
Hello Tom,

thank u for your fast reply!

Now I understand that i can not split easily my scripts into logical units but what i not understand is the documention not correct
or is my problem not similiar to the example in point 37.4.3 on http://www.postgresql.org/docs/7.4/s...larations.html
?

thank u for your help,
thomas
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3
"Thomas Chille" <th****@chille.de> writes:
Now I understand that i can not split easily my scripts into logical units but what i not understand is the documention not correct
or is my problem not similiar to the example in point 37.4.3 on http://www.postgresql.org/docs/7.4/s...larations.html
?


It is, but the only sort of operation you can apply to a row variable in
plpgsql is to extract a field from it. Other operations like calling a
function on it don't work (yet).

regards, tom lane

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

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

Nov 23 '05 #4
"Thomas Chille" <th****@chille.de> writes:
in every documentation(7.5 too) i can read that i can pass a row to a
plpgsql function. but how u know it does not work.
It does work ... from a SQL statement. The problem you are running into
is that a whole-row variable of a plpgsql function can't be passed as a
unit into a SQL statement. (I thought this limitation was documented
somewhere in the plpgsql chapter, but right at the moment I can't find
anything about it.)
And if the documentation is wrong, is this normal for the postgres-docs?


The documentation is not perfect.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5
Hi Tom,

sorry for asking again on the same issue but i try to figure out how i have to deal with and how strong i can trust the
postgres-docs and -books.

in every documentation(7.5 too) i can read that i can pass a row to a plpgsql function. but how u know it does not work. i am still
thinking i am wrong or are really all that docs wrong?

And if the documentation is wrong, is this normal for the postgres-docs?

I dont wanna steal your time, but i can not understand it.

regards
thomas
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Mark Mikulec | last post by:
Hi there, I wonder if anyone can shed some light on a very frustrating problem. I'm running a debian linux 3.0 "woody" server, nothing special, with the latest version of postres that apt-get...
1
by: Amin Schoeib | last post by:
Hi, When I Try to write Functions I always become the error That the language plpgsql doesn't exist. I tried it this way: createlang plpgsql template1 createlang -l template1 Procedural...
5
by: Thomas LeBlanc | last post by:
I copied an example from the help: CREATE FUNCTION somefunc() RETURNS integer AS ' DECLARE quantity integer := 30; BEGIN RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 30...
0
by: Steve Wampler | last post by:
Hmmm, I've always used plpgsql.so (also formerly known as libplpgsql.so, I think...) as in: CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler AS '/usr/lib/plpgsql.so',...
10
by: lnd | last post by:
After copied pg database from one PC to another -I could not find plpgsql function(s) in the copied database. -had to instal plpgsql language handler again -whilst tables and data moved...
4
by: Bill Moran | last post by:
I've got a bit of a strange problem that's causing me some MAJOR headaches. I'm developing the server-side of a large database application in PostgreSQL. This consists of a C daemon, and a LOT...
2
by: David Boone | last post by:
I've been trying to create functions with postgres, but it seems that queries run within a function take wayyy too long to complete. The increased time seems to be in the actual queries, not...
2
by: Mark Cave-Ayland | last post by:
Hi everyone, I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2 that given a tree node id (ictid) will return all the nodes below it in the tree, one row per node. When I try...
0
by: sripathy sena | last post by:
Hi, I am trying to install OPenacs with postgres 7.4.3 as the database. The openacs requires plpgsql to be installed. When I try to do this by running "CREATELANG plpgsql template1". I get a...
9
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.