By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,332 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,332 IT Pros & Developers. It's quick & easy.

PLpgSQL-Problem

P: n/a
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
Share this Question
Share on Google+
5 Replies

P: n/a
"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

P: n/a
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

P: n/a
"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

P: n/a
"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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.