problem with pl/pgsql

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

Предыдущее
От: Thomas Swan
Дата:
Сообщение: Re: MySql 'REPLACE'
Следующее
От: Hans-Jürgen Schönig
Дата:
Сообщение: SET SEQSCAN TO OFF - error