pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Nielsen <pgsql@mail.tcu-inc.com>
To: pgsql-sql@postgresql.org
Subject: problems with pl/pgsql
Date: Tue, 24 Apr 2001 20:29:11 -0400 (EDT)
Message-ID: <200104250029.UAA00376@mail.tcu-inc.com> (raw)

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






Message-ID: <200104250029.UAA00376@mail.tcu-inc.com>
Permalink:  ../200104250029.UAA00376@mail.tcu-inc.com/
Also on:    postgresql.org/message-id/200104250029.UAA00376@mail.tcu-inc.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pgsql@mail.tcu-inc.com
  Subject: Re: problems with pl/pgsql
  In-Reply-To: <200104250029.UAA00376@mail.tcu-inc.com>
  References: <200104250029.UAA00376@mail.tcu-inc.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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