Thread: plpgsql
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.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
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;' languageplpgsql; 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: > 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? > >
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