Lists: | pgsql-general |
---|
From: | "Thomas Chille" <thomas(at)chille(dot)de> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | PLpgSQL-Problem |
Date: | 2004-04-15 14:55:28 |
Message-ID: | 015c01c422f9$b17655a0$500a0a0a@spoon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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;
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Thomas Chille" <thomas(at)chille(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PLpgSQL-Problem |
Date: | 2004-04-15 23:42:52 |
Message-ID: | 22339.1082072572@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Thomas Chille" <thomas(at)chille(dot)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
From: | "Thomas Chille" <thomas(at)chille(dot)de> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PLpgSQL-Problem |
Date: | 2004-04-16 09:29:03 |
Message-ID: | 01f601c42395$423f4b00$500a0a0a@spoon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/static/plpgsql-declarations.html
?
thank u for your help,
thomas
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Thomas Chille" <thomas(at)chille(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PLpgSQL-Problem |
Date: | 2004-04-16 14:13:41 |
Message-ID: | 28864.1082124821@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Thomas Chille" <thomas(at)chille(dot)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/static/plpgsql-declarations.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
From: | "Thomas Chille" <thomas(at)chille(dot)de> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PLpgSQL-Problem |
Date: | 2004-04-17 16:32:35 |
Message-ID: | 030f01c42499$974c6920$500a0a0a@spoon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Thomas Chille" <thomas(at)chille(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PLpgSQL-Problem |
Date: | 2004-04-17 16:46:17 |
Message-ID: | 10971.1082220377@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Thomas Chille" <thomas(at)chille(dot)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