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