pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with PL/pgSQL
11+ messages / 9 participants
[nested] [flat]
* problem with PL/pgSQL
@ 1999-06-24 10:21 Tiberiu Craciun <tiberiu@akela.ro>
0 siblings, 2 replies; 11+ messages in thread
From: Tiberiu Craciun @ 1999-06-24 10:21 UTC (permalink / raw)
To: pgsql-sql@hub.org
I have a table and a function defined like that :
create table trafic (
statie text,
traf_in int8 default 0,
traf_out int8 default 0
);
create function addtrafic (text, int8, int2) returns int2 as '
declare
statia alias for $1 ;
traficul alias for $2 ;
directia alias for $3 ;
begin
select * from trafic where statie= $1;
if not found then
insert into trafic (statie) values( $1 );
endif;
if $3 < 1 then
update trafic set traf_in=traf_in+ $2 where statie= $1;
else
update trafic set traf_out=traf_out+ $2 where statie= $1;
endif;
return $3 ;
end;
' language 'plpgsql';
when I run this SQL :
select addtrafic('station1',10,0) as test;
I got an error like that :
NOTICE: plpgsql: ERROR during compile of addtrafic near line 20
ERROR: parse error at or near ";"
and i don't know what is wrong, can someone help me ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] problem with PL/pgSQL
@ 1999-06-24 11:51 Eric BASIER <basier@ipgp.jussieu.fr>
parent: Tiberiu Craciun <tiberiu@akela.ro>
1 sibling, 0 replies; 11+ messages in thread
From: Eric BASIER @ 1999-06-24 11:51 UTC (permalink / raw)
To: Tiberiu Craciun <tiberiu@akela.ro>; +Cc: pgsql-sql@hub.org
I insert my response in your text
Tiberiu Craciun wrote:
> I have a table and a function defined like that : create table trafic
> (
> statie text,
> traf_in int8 default 0,
> traf_out int8 default 0
> ); create function addtrafic (text, int8, int2) returns int2 as '
> declare
> statia alias for $1 ;
> traficul alias for $2 ;
> directia alias for $3 ;
> begin
> select * from trafic where statie= $1;
> if not found then
> insert into trafic (statie) values( $1 );
there is an error here ^
the good sentence is : insert into trafic (statie) values ($1) ;
> endif;
> if $3 < 1 then
> update trafic set traf_in=traf_in+ $2 where statie=
> $1;
> else
> update trafic set traf_out=traf_out+ $2 where statie=
> $1;
> endif;
> return $3 ;
> end;
> ' language 'plpgsql'; when I run this SQL : select
> addtrafic('station1',10,0) as test; I got an error like that : NOTICE:
> plpgsql: ERROR during compile of addtrafic near line 20ERROR: parse
> error at or near ";" and i don't know what is wrong, can someone help
> me ?
--
Basier Eric e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] problem with PL/pgSQL
@ 1999-06-24 12:08 José Soares <jose@sferacarta.com>
parent: Tiberiu Craciun <tiberiu@akela.ro>
1 sibling, 0 replies; 11+ messages in thread
From: José Soares @ 1999-06-24 12:08 UTC (permalink / raw)
To: Tiberiu Craciun <tiberiu@akela.ro>; +Cc: pgsql-sql@hub.org
Your function have some bugs;
1) The parser doesn't understand keywords like ENDIF but END IF
(two words)
2) You must to use keyword INTO with the select command
try this one instead:
create function addtrafic (text, int8, int2) returns int2 as '
declare
boo text;
statia alias for $1 ;
traficul alias for $2 ;
directia alias for $3 ;
begin
select statie from trafic into boo where statie= $1;
if not found then
insert into trafic (statie) values( $1 );
end if;
if $3 < 1 then
update trafic set traf_in=traf_in+ $2 where statie= $1;
else
update trafic set traf_out=traf_out+ $2 where statie=
$1;
end if;
return $3 ;
end;
' language 'plpgsql';
Tiberiu Craciun ha scritto:
> Part 1.1 Type: Plain Text (text/plain)
> Encoding: quoted-printable
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re:Re: [SQL] problem with PL/pgSQL
@ 1999-06-24 13:10 Tiberiu Craciun <tiberiu@akela.ro>
0 siblings, 0 replies; 11+ messages in thread
From: Tiberiu Craciun @ 1999-06-24 13:10 UTC (permalink / raw)
To: Eric BASIER <basier@ipgp.jussieu.fr>; +Cc: pgsql-sql@hub.org
I don't think that is, because the error is still there.
-----Original Message-----
From: Eric BASIER <basier@ipgp.jussieu.fr>
To: Tiberiu Craciun <tiberiu@akela.ro>
Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
Date: jeudi 24 juin 1999 19:51
Subject: Re: [SQL] problem with PL/pgSQL
>I insert my response in your text
>Tiberiu Craciun wrote:
>
>> I have a table and a function defined like that : create table trafic
>> (
>> statie text,
>> traf_in int8 default 0,
>> traf_out int8 default 0
>> ); create function addtrafic (text, int8, int2) returns int2 as '
>> declare
>> statia alias for $1 ;
>> traficul alias for $2 ;
>> directia alias for $3 ;
>> begin
>> select * from trafic where statie= $1;
>> if not found then
>> insert into trafic (statie) values( $1 );
>
> there is an error here ^
>the good sentence is : insert into trafic (statie) values ($1) ;
>
>
>
>> endif;
>> if $3 < 1 then
>> update trafic set traf_in=traf_in+ $2 where statie=
>> $1;
>> else
>> update trafic set traf_out=traf_out+ $2 where statie=
>> $1;
>> endif;
>> return $3 ;
>> end;
>> ' language 'plpgsql'; when I run this SQL : select
>> addtrafic('station1',10,0) as test; I got an error like that : NOTICE:
>> plpgsql: ERROR during compile of addtrafic near line 20ERROR: parse
>> error at or near ";" and i don't know what is wrong, can someone help
>> me ?
>
>
>
>--
>Basier Eric e-mail : basier@ipgp.jussieu.fr
>http://geoscope.ipgp.jussieu.fr
>IPGP Observatoires Sismologie-Volcanologie
>4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Problem with PL/pgSQL
@ 2000-02-08 13:46 Vladimir Terziev <vlady@school.digsys.bg>
0 siblings, 3 replies; 11+ messages in thread
From: Vladimir Terziev @ 2000-02-08 13:46 UTC (permalink / raw)
To: pgsql-sql
Hi
I try to create the next function:
create function ttt() RETURNS OPAQUE AS '
BEGIN
RAISE EXCEPTION "Hello"
END' language 'plpgsql';
The result is:
ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal and the created procedural languages.
I have postgres SQL ver. 6.5.2.
What's going on?
Vladimir
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Problem with PL/pgSQL
@ 2000-02-08 14:09 Vince Gonzalez <vince@nycrc.net>
parent: Vladimir Terziev <vlady@school.digsys.bg>
2 siblings, 0 replies; 11+ messages in thread
From: Vince Gonzalez @ 2000-02-08 14:09 UTC (permalink / raw)
To: Vladimir Terziev <vlady@school.digsys.bg>; +Cc: pgsql-sql
On Tue, Feb 08, 2000 at 03:46:14PM +0200, Vladimir Terziev wrote:
> I try to create the next function:
<snip>
> The result is:
> ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> Recognized languages are sql, C, internal and the created procedural languages.
You need to do createlang on the table, like so:
$ createlang plpgsql test
Where test is the database.
--vince
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Problem with PL/pgSQL
@ 2000-02-08 14:48 Vladimir Terziev <vlady@school.digsys.bg>
0 siblings, 0 replies; 11+ messages in thread
From: Vladimir Terziev @ 2000-02-08 14:48 UTC (permalink / raw)
To: pgsql-sql
Thanks all!
Vladimir
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Problem with PL/pgSQL
@ 2000-02-08 15:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vladimir Terziev <vlady@school.digsys.bg>
2 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2000-02-08 15:57 UTC (permalink / raw)
To: Vladimir Terziev <vlady@school.digsys.bg>; +Cc: pgsql-sql
"Vladimir Terziev" <vlady@school.digsys.bg> writes:
> ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> What's going on?
You didn't install plpgsql into your database. It's optional (just
in case a dbadmin doesn't want it available for some reason).
See the createlang script.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Problem with PL/pgSQL
@ 2000-02-08 16:04 Jan Wieck <wieck@debis.com>
parent: Vladimir Terziev <vlady@school.digsys.bg>
2 siblings, 0 replies; 11+ messages in thread
From: wieck@debis.com @ 2000-02-08 16:04 UTC (permalink / raw)
To: Vladimir Terziev <vlady@school.digsys.bg>; +Cc: pgsql-sql
>
>
> Hi
>
>
> I try to create the next function:
>
> create function ttt() RETURNS OPAQUE AS '
> BEGIN
> RAISE EXCEPTION "Hello"
> END' language 'plpgsql';
>
> The result is:
>
> ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> Recognized languages are sql, C, internal and the created procedural languages.
>
>
> I have postgres SQL ver. 6.5.2.
>
>
> What's going on?
As the message says, only the created procedural languages
can be recognized. You haven't enabled the language and it
isn't installed by default. Use
createlang <dbname>
to install it in your database. If you install it in the
template1 database, than all subsequent created databases
automatically inherit this.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 11+ messages in thread
* problem with pl/pgsql
@ 2001-04-24 23:39 Mark Nielsen <pgsql@mail.tcu-inc.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Nielsen @ 2001-04-24 23:39 UTC (permalink / raw)
To: pgsql-sql
hey guys,
I am writing an article about using Perl inside sql commands.
I am not having a problem with perl, but with pl/pgsql.
The documentation for pl/pgsql isn't helping me out, although I am sure
I will figure it out eventually.
Here is the perl function,
CREATE FUNCTION search_name(employee,text,integer) RETURNS text AS '
my $emp = shift;
my $Text = shift;
my $Case = shift;
if (($Case > 0) && ($emp->{''name''} =~ /\\Q$Text\\E/i))
{ return $emp->{''name''}; }
elsif ($Case > 0) {return "";}
elsif ($emp->{''name''} =~ /\\Q$Text\\E/)
{ return $emp->{''name''}; }
else { return "";}
' LANGUAGE 'plperl';
insert into EMPLOYEE values ('John Doe',10000,1);
insert into EMPLOYEE values ('Jane Doe',10000,1);
select name,search_name(employee,'j',0) from employee;
select name,search_name(employee,'j',1) from employee;
select name from employee where search_name(employee,'j',1) = name;
select name from employee where search_name(employee,'j',0) = name;
I know these functions aren't elegant, but oh well.
Here isthe pl/pgsql
CREATE FUNCTION insert_name(text)
RETURNS integer AS '
DECLARE
rec1 record; text1 text;
BEGIN
text1 := $1;
SELECT INTO rec1 count(name)
FROM employee
where search_name(employee,text1,0) = name
limit 1;
IF rec1.count = 0
THEN insert into employee (name) values (text1);
return 1;
END IF;
return 0;
END;
' LANGUAGE 'plpgsql';
What I am trying to do is set something up where it will only
insert a value if it doesn't exist.
I want it to return either 0 or 1.
However, it returns 6 rows if there are 6 entries as 0 or 1.
In my perl statement, it doesn't return anythng if a row
doesn't match. I want the pl/pglsq statement to not return
anything for any rows whatsoever, and to return either a 1 or 0
at the very end of the function.
How do I execute sql commands inside pl/pgsql so that
the that they remain hidden?
I plan on using pl/pgsql a lot now. I really want to combine
perl and pl/pgsql as standard sql options aren't that great.
Thanks!
Mark
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: problem with pl/pgsql
@ 2001-04-26 15:17 Josh Berkus <josh@agliodbs.com>
parent: Mark Nielsen <pgsql@mail.tcu-inc.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2001-04-26 15:17 UTC (permalink / raw)
To: Mark Nielsen <pgsql@mail.tcu-inc.com>; +Cc: pgsql-sql
Mark,
I'd love to help you with your PL/pgSQL function, but since I don't
read Perl, I can't tell what you're trying to do. Can you spell it out
more explicitly?
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2001-04-26 15:17 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-24 10:21 problem with PL/pgSQL Tiberiu Craciun <tiberiu@akela.ro>
1999-06-24 11:51 ` Eric BASIER <basier@ipgp.jussieu.fr>
1999-06-24 12:08 ` José Soares <jose@sferacarta.com>
1999-06-24 13:10 Re:Re: [SQL] problem with PL/pgSQL Tiberiu Craciun <tiberiu@akela.ro>
2000-02-08 13:46 Problem with PL/pgSQL Vladimir Terziev <vlady@school.digsys.bg>
2000-02-08 14:09 ` Re: [SQL] Problem with PL/pgSQL Vince Gonzalez <vince@nycrc.net>
2000-02-08 15:57 ` Re: [SQL] Problem with PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
2000-02-08 16:04 ` Re: [SQL] Problem with PL/pgSQL Jan Wieck <wieck@debis.com>
2000-02-08 14:48 Re: [SQL] Problem with PL/pgSQL Vladimir Terziev <vlady@school.digsys.bg>
2001-04-24 23:39 problem with pl/pgsql Mark Nielsen <pgsql@mail.tcu-inc.com>
2001-04-26 15:17 ` Re: problem with pl/pgsql Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox