pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgsql question
15+ messages / 11 participants
[nested] [flat]
* PL/pgsql question
@ 2001-06-12 13:59 Andrea Suisani <suisani@demetra.com>
0 siblings, 0 replies; 15+ messages in thread
From: Andrea Suisani @ 2001-06-12 13:59 UTC (permalink / raw)
To: pgsql-sql
hi,
i define a function using pgacces with pl/pgsql.
it seems that the queries define in the function wasn't executed.
if I try to excute the same queries from the psql everything, really, works
fine.
the body of the function is:
-----------------------------------
Declare
rec_struttura record;
estra_capo float8;
estra_non float8;
rand_doub float8;
row float8;
capo int;
i int;
rand_max float8;
Begin
i:=0;
For rec_struttura in Select * From struttura_campione Loop
i:=i+1;
prov := rec_struttura.cod_prov;
estra_capo := rec_struttura.num_capo;
estra_non := rec_struttura.num_non_capo;
capo := 1;
row:=prova_rand(prov,capo,1)::float8;
rand_doub:=(estra_capo/row);
if i = 1 then
create table tmp as select * from veneto where cod_prov=prov and
capoluo=capo and rand < rand_doub and occupato = false limit estra_capo;
end if;
if i > 1 then
insert into tmp select * from veneto where cod_prov=prov and capoluo=capo
and rand < rand_doub and occupato = false limit estra_capo ;
end if;
insert into campione_out select note, prefisso, telefono1 as telefono,
cap, cod_com, cod_prov, citta, nome_cogno as nome_cognome, capoluo,
estrazione as ultimo_acesso, rand from tmp;
insert into tab_estrazioni (prefisso,telefono) select prefisso as
presisso, telefono1 as telefono from tmp;
delete from tmp;
rand_max:=prova_rand(prov,capo,2);
update veneto set occupato = true, estrazione = now() where (capoluo =
capo and cod_prov = prov) and (rand <= rand_max) and occupato=false ;
end loop;
return rand_doub;
end;
--------------------------------------------------------------
no errors are report in log file during the excution of this function.
What should i change in the function?
Thanks in advance for any advice
Andrea
P.S. i'm sorry for my bad english
^ permalink raw reply [nested|flat] 15+ messages in thread
* PL/pgSQL question
@ 2002-03-15 23:41 Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 15+ messages in thread
From: Dmitry Tkach @ 2002-03-15 23:41 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
Could somebody help me with PL/pgsql please?
Does it work with pg's arrays (_int4)? What is the syntax?
I tried something like
declare a _int4;
begin a[0] := 1; end;
But it complains about a syntax error near "["...
Thanks a lot!
Dima
^ permalink raw reply [nested|flat] 15+ messages in thread
* Pl/pgSQL Question.
@ 2002-04-25 04:34 Bruno Ricardo Sacco <bsacco@hypernet.com.py>
0 siblings, 0 replies; 15+ messages in thread
From: Bruno Ricardo Sacco @ 2002-04-25 04:34 UTC (permalink / raw)
To: pgsql-sql
Im traing to make a pl/pgsql function that display records of a given table.
I pass the name of the table has an argument for the function.
Using the,
FOR myRec IN SELECT xxxxxxxxxxx LOOP
do some of the work, but I still need to know the names of the fields.
for example: myRec.name
there is a way to do same thing like :
myRec[0]
to reference fields in the record ?
I can retriebe the names of the table fields from then system tables, but
that din't help me.
Anyome knows how ?
Sorry for me english.....
-------------------------------------------------------------------------------------------------------------------------------
Bruno Ricardo Sacco
IT Manager
HyperNET ISP
^ permalink raw reply [nested|flat] 15+ messages in thread
* Pl/pgSQL Question.
@ 2002-04-25 04:44 Bruno Ricardo Sacco <bsacco@hypernet.com.py>
0 siblings, 0 replies; 15+ messages in thread
From: Bruno Ricardo Sacco @ 2002-04-25 04:44 UTC (permalink / raw)
To: pgsql-sql
Im traing to make a pl/pgsql function that display records of a given table.
I pass the name of the table has an argument for the function.
Using the,
FOR myRec IN SELECT xxxxxxxxxxx LOOP
do some of the work, but I still need to know the names of the fields.
for example: myRec.name
there is a way to do same thing like :
myRec[0]
to reference fields in the record ?
I can retriebe the names of the table fields from then system tables, but
that din't help me.
Anyome knows how ?
Sorry for me english.....
-------------------------------------------------------------------------------------------------------------------------------
Bruno Ricardo Sacco
IT Manager
HyperNET ISP
^ permalink raw reply [nested|flat] 15+ messages in thread
* pl/pgsql question
@ 2002-12-18 04:12 Tim Perdue <tim@perdue.net>
2002-12-18 04:26 ` Re: pl/pgsql question Tom Lane <tgl@sss.pgh.pa.us>
2002-12-18 04:29 ` Re: pl/pgsql question Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 2 replies; 15+ messages in thread
From: Tim Perdue @ 2002-12-18 04:12 UTC (permalink / raw)
To: pgsql-sql
I have created a function in pl/pgsql to modify a row before it gets put
into the database, but it seems my modification is being ignored, and
the unmodified row is being inserted.
I have confirmed with this RAISE EXCEPTION that my "NEW" row is modified
properly, however it is not being stored in the db.
NEW.start_date := NEW.start_date+delta;
-- RAISE EXCEPTION ''new start date: % '',NEW.start_date;
NEW.end_date := NEW.end_date+delta;
It's probably something very obvious, but I'm mystified.
Tim
--
-- Function to enforce dependencies in the table structure
--
CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
DECLARE
dependent RECORD;
delta INTEGER;
BEGIN
--
-- First make sure we start on or after end_date of tasks
-- that we depend on
--
FOR dependent IN SELECT * FROM project_depend_vw
WHERE
project_task_id=NEW.project_task_id LOOP
--
-- See if the task we are dependent on
-- ends after we are supposed to start
--
IF dependent.end_date > NEW.start_date THEN
delta := dependent.end_date-NEW.start_date;
-- RAISE EXCEPTION ''delta: % '',delta;
NEW.start_date := NEW.start_date+delta;
-- RAISE EXCEPTION ''new start date: %
'',NEW.start_date;
NEW.end_date := NEW.end_date+delta;
END IF;
END LOOP;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task
FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pl/pgsql question
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
@ 2002-12-18 04:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Tom Lane @ 2002-12-18 04:26 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
> I have created a function in pl/pgsql to modify a row before it gets put
^^^^^^^^^^^^^^^^^^^
> into the database, but it seems my modification is being ignored, and
> the unmodified row is being inserted.
> CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task
^^^^^^^^^^^^
> FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();
I believe I see your problem ...
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pl/pgsql question
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
@ 2002-12-18 04:29 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-12-18 14:02 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
1 sibling, 1 reply; 15+ messages in thread
From: Ludwig Lim @ 2002-12-18 04:29 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
--- Tim Perdue <tim@perdue.net> wrote:
> I have created a function in pl/pgsql to modify a
> row before it gets put
> into the database, but it seems my modification is
> being ignored, and
> the unmodified row is being inserted.
>
> I have confirmed with this RAISE EXCEPTION that my
> "NEW" row is modified
> properly, however it is not being stored in the db.
>
> NEW.start_date := NEW.start_date+delta;
> -- RAISE EXCEPTION ''new start date: %
> '',NEW.start_date;
> NEW.end_date := NEW.end_date+delta;
>
> It's probably something very obvious, but I'm
> mystified.
>
> CREATE TRIGGER projtask_insert_depend_trig AFTER
> INSERT ON project_task
> FOR EACH ROW EXECUTE PROCEDURE
> projtask_insert_depend();
>
>
Try changing the "AFTER" to "BEFORE"
CREATE TRIGGER projtask_insert_depend_trig BEFORE...
Changes made to the "NEW" will not be reflect in the
AFTER trigger since, the row is already inserted.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pl/pgsql question
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 04:29 ` Re: pl/pgsql question Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2002-12-18 14:02 ` Tim Perdue <tim@perdue.net>
2002-12-18 16:30 ` Re: pl/pgsql question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tim Perdue @ 2002-12-18 14:02 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Ludwig Lim wrote:
> Try changing the "AFTER" to "BEFORE"
>
> CREATE TRIGGER projtask_insert_depend_trig BEFORE...
>
> Changes made to the "NEW" will not be reflect in the
> AFTER trigger since, the row is already inserted.
Thanks, however this seems to present a different problem now.
FOR dependon IN SELECT * FROM project_depend_vw
WHERE
project_task_id=NEW.project_task_id LOOP
That loop apparently does not find any matching rows, which would have
been inserted just before this row was, inside the same transaction.
It was successfully finding those rows before, when the trigger was
AFTER INSERT. If I manually select those rows after the query is
committed, I am able to pull up the matching rows.
Tim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pl/pgsql question
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 04:29 ` Re: pl/pgsql question Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-12-18 14:02 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
@ 2002-12-18 16:30 ` Josh Berkus <josh@agliodbs.com>
2002-12-18 16:44 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
0 siblings, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2002-12-18 16:30 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Tim,
> That loop apparently does not find any matching rows, which would
> have been inserted just before this row was, inside the same
> transaction.
>
> It was successfully finding those rows before, when the trigger was
> AFTER INSERT. If I manually select those rows after the query is
> committed, I am able to pull up the matching rows.
I think that triggers are probably not a good strategy for the kind of
calculation you're doing. I'd suggest instead a middleware module or a
"data push" function which would bundle all of the calculation logic
before calling any of the inserts.
-Josh
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pl/pgsql question
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 04:29 ` Re: pl/pgsql question Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-12-18 14:02 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 16:30 ` Re: pl/pgsql question Josh Berkus <josh@agliodbs.com>
@ 2002-12-18 16:44 ` Tim Perdue <tim@perdue.net>
0 siblings, 0 replies; 15+ messages in thread
From: Tim Perdue @ 2002-12-18 16:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Josh Berkus wrote:
> Tim,
>
>
>>That loop apparently does not find any matching rows, which would
>>have been inserted just before this row was, inside the same
>>transaction.
>>
>>It was successfully finding those rows before, when the trigger was
>>AFTER INSERT. If I manually select those rows after the query is
>>committed, I am able to pull up the matching rows.
>
>
> I think that triggers are probably not a good strategy for the kind of
> calculation you're doing. I'd suggest instead a middleware module or a
> "data push" function which would bundle all of the calculation logic
> before calling any of the inserts.
Yeah, but this is so much cooler. ;-)
Essentially this would be like recursion to push back/pull forward tasks
which are dependent on each other. The "UPDATE" trigger I wrote is about
5x longer.
I guess I can push this back into the PHP code and do a recusive
function call, but that seems less sexy.
Tim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/pgSQL question
@ 2003-02-02 21:59 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2003-02-02 21:59 UTC (permalink / raw)
To: mail.luckydigital.com <jim@luckydigital.com>; +Cc: pgsql-sql
Jim,
> Yes i'm aware this it is possible in 7.3 - can someone please confirm its
> not possible in 7.2 or provide me with an example of how to go about it.
It is sort of possible in 7.2, as a function can return a cursor to an
interface language capable of handling cursors. However, implementation is
quite cumbersome, and I won't try to detail it here, particularly since that
language and method have been obsolesced and would force you to re-write your
code when you do upgrade.
If this feature is important to you, I strongly recommend that you upgrade to
7.3.1 now.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* PL/PgSQL Question
@ 2003-04-25 15:52 Brian Knox <laotse@aol.net>
2003-04-26 08:53 ` Re: PL/PgSQL Question A.Bhuvaneswaran <bhuvansql@myrealbox.com>
0 siblings, 1 reply; 15+ messages in thread
From: Brian Knox @ 2003-04-25 15:52 UTC (permalink / raw)
To: pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/PgSQL Question
2003-04-25 15:52 PL/PgSQL Question Brian Knox <laotse@aol.net>
@ 2003-04-26 08:53 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
0 siblings, 0 replies; 15+ messages in thread
From: A.Bhuvaneswaran @ 2003-04-26 08:53 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
> I want to intercept that query with a rule, and with DO INSTEAD call a
> stored procedure and pass the values in the where clause to that stored
> proc? Is this possible? If so, how?
If this is the case, why don't you try a triggered procedure? Follow the
below link to learn howto write triggered procedures:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql-trigger.html
regards,
bhuvaneswaran
^ permalink raw reply [nested|flat] 15+ messages in thread
* PL/pgSQL question
@ 2008-02-14 13:23 Sebastian Ritter <ritter.sebastian@gmail.com>
2008-02-14 13:50 ` Re: PL/pgSQL question Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 15+ messages in thread
From: Sebastian Ritter @ 2008-02-14 13:23 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a question regarding functions. How can I return zero rows from a
function whose return type is a table row? I did the following test and it
did not work as expected:
CREATE OR REPLACE FUNCTION
fn_get_user (integer) RETURNS usertable AS '
DECLARE
in_userid ALIAS for $1;
resulter usertable%ROWTYPE;
BEGIN
IF in_userid IS NULL THEN
RAISE EXCEPTION ''No user provided'';
RETURN null;
END IF;
SELECT INTO resulter
usertable.*
FROM
usertable
WHERE
id = in_userid;
IF FOUND THEN
RETURN resulter;
ELSE
RETURN null;
END IF;
END;'LANGUAGE plpgsql;
>select * from fn_get_user(-1);
id | col1 | col2 | col3| name | email
----+------------+-------------+------------+--------
| | | | | |
(1 row)
This returns a null row. I am trying to make it behave such that it returns
zero rows like a straight select.
>select * from usertable where id =-1;
id | col1 | col2 | col3| name | email
----+------------+-------------+------------+--------
(0 rows)
Is this possible in anyway?
Regards,
Sebastian
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PL/pgSQL question
2008-02-14 13:23 PL/pgSQL question Sebastian Ritter <ritter.sebastian@gmail.com>
@ 2008-02-14 13:50 ` Milen A. Radev <milen@radev.net>
0 siblings, 0 replies; 15+ messages in thread
From: Milen A. Radev @ 2008-02-14 13:50 UTC (permalink / raw)
To: pgsql-sql
Sebastian Ritter написа:
> Hi all,
>
> I have a question regarding functions. How can I return zero rows from a
> function whose return type is a table row? I did the following test and it
> did not work as expected:
[...]
CREATE OR REPLACE FUNCTION foobar(boolean) RETURNS SETOF x AS $_$
DECLARE
res x%ROWTYPE;
BEGIN
if $1 THEN
RETURN NEXT res;
ELSE
RETURN;
END IF;
END;$_$
LANGUAGE plpgsql;
--
Milen A. Radev
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2008-02-14 13:50 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-12 13:59 PL/pgsql question Andrea Suisani <suisani@demetra.com>
2002-03-15 23:41 PL/pgSQL question Dmitry Tkach <dmitry@openratings.com>
2002-04-25 04:34 Pl/pgSQL Question. Bruno Ricardo Sacco <bsacco@hypernet.com.py>
2002-04-25 04:44 Pl/pgSQL Question. Bruno Ricardo Sacco <bsacco@hypernet.com.py>
2002-12-18 04:12 pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 04:26 ` Re: pl/pgsql question Tom Lane <tgl@sss.pgh.pa.us>
2002-12-18 04:29 ` Re: pl/pgsql question Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-12-18 14:02 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
2002-12-18 16:30 ` Re: pl/pgsql question Josh Berkus <josh@agliodbs.com>
2002-12-18 16:44 ` Re: pl/pgsql question Tim Perdue <tim@perdue.net>
2003-02-02 21:59 Re: PL/pgSQL question Josh Berkus <josh@agliodbs.com>
2003-04-25 15:52 PL/PgSQL Question Brian Knox <laotse@aol.net>
2003-04-26 08:53 ` Re: PL/PgSQL Question A.Bhuvaneswaran <bhuvansql@myrealbox.com>
2008-02-14 13:23 PL/pgSQL question Sebastian Ritter <ritter.sebastian@gmail.com>
2008-02-14 13:50 ` Re: PL/pgSQL question Milen A. Radev <milen@radev.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox