By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

plpgsql

P: n/a
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 | mm******@unl.edu.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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)

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

Nov 12 '05 #2

P: n/a
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?

---------------------------(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

Nov 12 '05 #3

P: n/a
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,oldVca mpo,
''biblioteca'',''titulo'',TG_RELNAME,TG_OP);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

(actualizarDiccionario is declared like this:
actualizarDiccionario(INT,INT,INT,INT,VARCHAR,VARC HAR,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 | mm******@unl.edu.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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4

P: n/a
Martin Marques <ma****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #5

P: n/a
El Lun 20 Oct 2003 10:54, escribió:
Martin Marques <ma****@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 | mm******@unl.edu.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

Nov 12 '05 #6

P: n/a
Martin Marques <ma****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.