plpgsql

[Martin Marques]

We are trying to make some things work with plpgsql. The problem is that I
built several functions that call one another, and I thought that the way of
calling it was just making the assign:

var:=func1(arg1 ,arg2);

which gave me an error near ")".

Now if I did the same, but like this:

PERFORM ''SELECT func1(arg1,arg2 )'';

it didn't give the error anymore. The problem was that the other function
(func1()) aparently didn't get executed (logs stop at the PERFORM).

Am I doing something wrong?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.ed u.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera]

Re: plpgsql

On Sat, Oct 18, 2003 at 06:48:10PM -0300, Martin Marques wrote:[color=blue]
> We are trying to make some things work with plpgsql. The problem is that I
> built several functions that call one another, and I thought that the way of
> calling it was just making the assign:
>
> var:=func1(arg1 ,arg2);[/color]

Have you tried plpgsql's SELECT INTO ?

FWIW this works for me:
alvh=> create function a() returns text as 'select ''foo''::text' language sql;
CREATE FUNCTION

alvh=> create or replace function b() returns text as 'declare b text; begin select into b a(); return b; end;' language plpgsql;
CREATE FUNCTION

alvh=> select b();
b
-----
foo
(1 registro)

alvh=> create or replace function b() returns text as 'declare b text; begin b := a(); return b; end;' language plpgsql;
CREATE FUNCTION
alvh=> select b();
b
-----
foo
(1 registro)


--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Por suerte hoy explotó el califont porque si no me habría muerto
de aburrido" (Papelucho)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Pavel Stehule]

Re: plpgsql

Hello

This works fine. I have PostgreSQL 7.4

CREATE OR REPLACE FUNCTION foo1(int, int) RETURNS int AS '
BEGIN
RETURN $1 + $2;
END; ' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION foo2() RETURNS boolean AS '
DECLARE i int;
BEGIN i := foo1(10,10);
RETURN i = 20;
END; ' LANGUAGE plpgsql;

testdb011=> \i pokus.sql
CREATE FUNCTION
CREATE FUNCTION
testdb011=> select foo2();
foo2
------
t
(1 øádka)





On Sat, 18 Oct 2003, Martin Marques wrote:
[color=blue]
> We are trying to make some things work with plpgsql. The problem is that I
> built several functions that call one another, and I thought that the way of
> calling it was just making the assign:
>
> var:=func1(arg1 ,arg2);
>
> which gave me an error near ")".
>
> Now if I did the same, but like this:
>
> PERFORM ''SELECT func1(arg1,arg2 )'';
>
> it didn't give the error anymore. The problem was that the other function
> (func1()) aparently didn't get executed (logs stop at the PERFORM).
>
> Am I doing something wrong?
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martin Marques]

Re: plpgsql

El Dom 19 Oct 2003 18:25, Alvaro Herrera escribió:[color=blue]
> On Sat, Oct 18, 2003 at 06:48:10PM -0300, Martin Marques wrote:[color=green]
> > We are trying to make some things work with plpgsql. The problem is that
> > I built several functions that call one another, and I thought that the
> > way of calling it was just making the assign:
> >
> > var:=func1(arg1 ,arg2);[/color]
>
> Have you tried plpgsql's SELECT INTO ?[/color]

OK, let me be more specific. I tried this aready with this error:

2003-10-20 09:28:05 [27039] ERROR: parser: parse error at or near ")" at
character 15
2003-10-20 09:28:05 [27039] WARNING: plpgsql: ERROR during compile of
objetosdatosact ualizadicc near line 2

Now, here are the specifications about my function.
I'm building a function that does things with the fields of each row inserted.
This function is called from a Trigger. Also, this function calls another
function with does the actual job (well, it really cals some other functions,
all writen in plpgsql).

Here's the code:

CREATE OR REPLACE FUNCTION objetosdatosAct ualizaDicc() RETURNS TRIGGER AS '
DECLARE
newPk INT;
oldPk INT;
newVcampo VARCHAR;
oldVcampo VARCHAR;
salida RECORD;
BEGIN
IF TG_OP = ''UPDATE'' OR TG_OP = ''INSERT'' THEN
newPk := NEW.codigo;
newVcampo := NEW.titulo;
END IF;
IF TG_OP = ''UPDATE'' OR TG_OP = ''DELETE'' THEN
oldPk := OLD.codigo;
oldVcampo := OLD.titulo;
END IF;
SELECT INTO salida
actualizarDicci onario(newPk,ne wVcampo,oldPk,o ldVcampo,
''biblioteca'', ''titulo'',TG_R ELNAME,TG_OP);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

(actualizarDicc ionario is declared like this:
actualizarDicci onario(INT,INT, INT,INT,VARCHAR ,VARCHAR,VARCHA R,VARCHAR)
)

This is what's giving me the error:

2003-10-20 09:28:05 [27039] ERROR: parser: parse error at or near ")" at
character 15
2003-10-20 09:28:05 [27039] WARNING: plpgsql: ERROR during compile of
objetosdatosact ualizadicc near line 2

If I change the last SELECT INTO for a PERFORM I don't get the error, but I
also don't get the things from actualizarDicci onario() done (as if it wasn't
executed).

--
09:28:01 up 17 days, 19:00, 3 users, load average: 0.33, 0.38, 0.36
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.ed u.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: plpgsql

Martin Marques <martin@bugs.un l.edu.ar> writes:[color=blue]
> OK, let me be more specific. I tried this aready with this error:[/color]
[color=blue]
> 2003-10-20 09:28:05 [27039] ERROR: parser: parse error at or near ")" at
> character 15
> 2003-10-20 09:28:05 [27039] WARNING: plpgsql: ERROR during compile of
> objetosdatosact ualizadicc near line 2[/color]

Line 2 of the function is not where your assignment is; there seems to
be something wrong with your first variable declaration. I'm not sure
what --- when I copied-and-pasted the text it worked fine. One
possibility is that you seem to have tabs rather than spaces between
the variable name and datatype --- if you are trying to feed this file
in via psql, that could possibly boomerang on you.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Martin Marques]

Re: plpgsql

El Lun 20 Oct 2003 10:54, escribió:[color=blue]
> Martin Marques <martin@bugs.un l.edu.ar> writes:[color=green]
> > OK, let me be more specific. I tried this aready with this error:
> >
> > 2003-10-20 09:28:05 [27039] ERROR: parser: parse error at or near ")"
> > at character 15
> > 2003-10-20 09:28:05 [27039] WARNING: plpgsql: ERROR during compile of
> > objetosdatosact ualizadicc near line 2[/color]
>
> Line 2 of the function is not where your assignment is; there seems to
> be something wrong with your first variable declaration. I'm not sure
> what --- when I copied-and-pasted the text it worked fine. One
> possibility is that you seem to have tabs rather than spaces between
> the variable name and datatype --- if you are trying to feed this file
> in via psql, that could possibly boomerang on you.[/color]

Great! I don't understand why I started putting tabs. The first 4 functions
have spaces between the variable name and the type.

That made it pass. I am now working on another function which is called from
this one.

Is there a standard way of debugging plpgsql code?

--
11:11:01 up 17 days, 20:43, 3 users, load average: 1.54, 1.68, 1.29
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.ed u.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------


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

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

[Tom Lane]

Re: plpgsql

Martin Marques <martin@bugs.un l.edu.ar> writes:[color=blue][color=green]
>> Line 2 of the function is not where your assignment is; there seems to
>> be something wrong with your first variable declaration.[/color][/color]
[color=blue]
> Great! I don't understand why I started putting tabs. The first 4 functions
> have spaces between the variable name and the type.[/color]

Okay. I've improved the error reporting here for 7.4. CVS tip now does

regression=# create function foo() returns int as '
regression'# declare
regression'# xyzint;
regression'# begin
regression'# ...
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo();
ERROR: invalid type name ""
CONTEXT: compile of PL/pgSQL function "foo" near line 2

which should be at least a little less confusing ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)