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
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. |