problem with pl/pgsql

From: Mark Nielsen <pgsql(at)mail(dot)tcu-inc(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: problem with pl/pgsql
Date: 2001-04-24 23:39:47
Message-ID: 200104242339.TAA32688@mail.tcu-inc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Nielsen 2001-04-25 00:29:11 problems with pl/pgsql
Previous Message John Oakes 2001-04-24 17:21:32 JDBC speed question.