problem with pl/pgsql
От | Mark Nielsen |
---|---|
Тема | problem with pl/pgsql |
Дата | |
Msg-id | 200104242339.TAA32688@mail.tcu-inc.com обсуждение исходный текст |
Ответы |
Re: problem with pl/pgsql
|
Список | 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
В списке pgsql-sql по дате отправления: