plpgsql

On Sat, Oct 18, 2003 at 06:48:10PM -0300, Martin Marques wrote:

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);

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)

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:

El Dom 19 Oct 2003 18:25, Alvaro Herrera escribió:

On Sat, Oct 18, 2003 at 06:48:10PM -0300, Martin Marques wrote:

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);

Have you tried plpgsql's SELECT INTO ?

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
objetosdatosactualizadicc 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 objetosdatosActualizaDicc() 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
actualizarDiccionario(newPk,newVcampo,oldPk,oldVcampo,
''biblioteca'',''titulo'',TG_RELNAME,TG_OP);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

(actualizarDiccionario is declared like this:
actualizarDiccionario(INT,INT,INT,INT,VARCHAR,VARCHAR,VARCHAR,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
objetosdatosactualizadicc 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 actualizarDiccionario() 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.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Martin Marques <martin@bugs.unl.edu.ar> writes:

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
objetosdatosactualizadicc near line 2

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

El Lun 20 Oct 2003 10:54, escribió:

Martin Marques <martin@bugs.unl.edu.ar> writes:

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
objetosdatosactualizadicc near line 2

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.

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.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Martin Marques <martin@bugs.unl.edu.ar> writes:

Line 2 of the function is not where your assignment is; there seems to
be something wrong with your first variable declaration.

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

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