pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problem 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