473,405 Members | 2,354 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

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 | 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
6 3951
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Mark Mikulec | last post by:
Hi there, I wonder if anyone can shed some light on a very frustrating problem. I'm running a debian linux 3.0 "woody" server, nothing special, with the latest version of postres that apt-get...
1
by: Rajesh Kumar Mallah | last post by:
Hi, profile_row profile_master%ROWTYPE; in a plpgsql function gives the error below tradein_clients=# SELECT general.create_accounts(); WARNING: plpgsql: ERROR during compile of...
0
by: Steve Wampler | last post by:
Hmmm, I've always used plpgsql.so (also formerly known as libplpgsql.so, I think...) as in: CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler AS '/usr/lib/plpgsql.so',...
10
by: lnd | last post by:
After copied pg database from one PC to another -I could not find plpgsql function(s) in the copied database. -had to instal plpgsql language handler again -whilst tables and data moved...
4
by: Bill Moran | last post by:
I've got a bit of a strange problem that's causing me some MAJOR headaches. I'm developing the server-side of a large database application in PostgreSQL. This consists of a C daemon, and a LOT...
14
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where...
0
by: sripathy sena | last post by:
Hi, I am trying to install OPenacs with postgres 7.4.3 as the database. The openacs requires plpgsql to be installed. When I try to do this by running "CREATELANG plpgsql template1". I get a...
9
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make...
1
by: Karl O. Pinc | last post by:
FYI, mostly. But I do have questions as to how to write code that will continue to work in subsequent postgresql versions. See code below. begintest() uses EXIT to exit a BEGIN block from...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.