[SQL] Calling functions from within pl/pgsql
I noticed that in pl/pgsql simply result := funcname(func,args,...); just works. Should i rely on it or take it as a coincidence? :) -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Calling functions from within pl/pgsql
On 04/12/2007, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > I noticed that in pl/pgsql simply > result := funcname(func,args,...); > just works. > Should i rely on it or take it as a coincidence? :) You found assignment :). Sure, you can use it. http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT Regards Pavel Stehule > -- > Achilleas Mantzios > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Calling functions from within pl/pgsql
Στις Tuesday 04 December 2007 14:59:43 ο/η Pavel Stehule έγραψε: > On 04/12/2007, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > > I noticed that in pl/pgsql simply > > result := funcname(func,args,...); > > just works. > > Should i rely on it or take it as a coincidence? :) > > You found assignment :). Sure, you can use it. > http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPG >SQL-STATEMENTS-ASSIGNMENT Sure, i missed the "As explained above, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value." part. Thanx. > > Regards > Pavel Stehule > > > -- > > Achilleas Mantzios > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Calling functions from within pl/pgsql
Hello a := exprx is internally translated : get result of SELECT exprx and store it to a so you can write select sin(10); then you can a := sin(10) but you can too use subselect like: SELECT (SELECT v FROM foo LIMIT 1) ~ you can write a := (SELECT v FROM foo LIMIT 1); that is equal to SELECT INTO a v FROM foo Pavel > > http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPG > >SQL-STATEMENTS-ASSIGNMENT > > Sure, i missed the > "As explained above, the expression in such a statement is evaluated by means > of an SQL SELECT command sent to the main database engine. The expression > must yield a single value." > part. > Thanx. > > > > > Regards > > Pavel Stehule > > > > > -- > > > Achilleas Mantzios > > > > > > ---(end of broadcast)--- > > > TIP 6: explain analyze is your friend > > > > -- > Achilleas Mantzios > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] marking tuples
Hi, I am implementing a new algorithm inside postgresql for a side research project. I am just wondering if anyone may give me few hints about the following problem: Consider the following plan: NewAggregate ->NewJoin ->NewJoin ->NewJoin ->SeqScan ->SeqScan ->SeqScan ->SeqScan In my algorithm, each NewJoin node may "mark" its ps_ResultTupleSlot with either red/green. I need that mark to flow in the pipeline so that i can collect them at the top aggregate. Any hints about how to keep such mark is greatly appreciated. Thanks a lot, Ehab _ You keep typing, we keep giving. Download Messenger and join the i’m Initiative now. http://im.live.com/messenger/im/home/?source=TAGLM
[SQL] Error OID
Hi all,
I have a big table:
CREATE TABLE Jurnal (Rekening Char(4) NOT NULL, Debet Numeric(9) NOT
NULL, Kredit Numeric(9) NOT NULL)
INSERT INTO Jurnal (Rekening,Debet,Kredit) values ('0001',1,2);
INSERT INTO Jurnal (Rekening,Debet,Kredit) values ('0002',3,4);
I create a function:
CREATE OR REPLACE FUNCTION ReProses()
RETURNS BOOLEAN
AS $$
DECLARE
nHasil Numeric;
BEGIN
CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP
AS
SELECT Rekening, SUM(Debet) AS Debet, SUM(Kredit) AS Kredit
FROM Jurnal
GROUP BY Rekening;
SELECT COALESCE(SUM(Debet - Kredit), 0)
INTO nHasil
FROM tmpTtlRekening;
-- bla,bla
RETURN '1';
END;
$$ LANGUAGE 'plpgsql';
Execute Query:
SELECT ReProses()
Result is fine, but when i execute again
SELECT ReProses()
ERROR: relation with OID 41573 does not exist
Can anyone help me
thanks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Error OID
Yohanes Purnomo a écrit : > [...] > I create a function: > > CREATE OR REPLACE FUNCTION ReProses() > RETURNS BOOLEAN > AS $$ > > DECLARE > nHasil Numeric; > > BEGIN > CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP > AS > SELECT Rekening, SUM(Debet) AS Debet, SUM(Kredit) AS Kredit > FROM Jurnal > GROUP BY Rekening; > > SELECT COALESCE(SUM(Debet - Kredit), 0) > INTO nHasil > FROM tmpTtlRekening; > > -- bla,bla > RETURN '1'; > END; > $$ LANGUAGE 'plpgsql'; > > > Execute Query: > > SELECT ReProses() > > Result is fine, but when i execute again > When you run a function for the first time in the session, PostgreSQL keeps in cache some information... query plans for example. > SELECT ReProses() > > ERROR: relation with OID 41573 does not exist > When you run it a second time, it uses the query plan in cache. As tmpTtlRekening has been drop at the end of the first run, its OID will change but the old OID is still in cache. So PostgreSQL tries to get information from the old temp table. If you don't want that PostgreSQL put the query plan in cache, use the EXECUTE statement. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
