Re: RE: Create table in functions - Mailing list pgsql-sql

Andreas Tille wrote:
> On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
>
> > Hmm,Andreas's original function seems to contain other statements.
> > If the function contains DML statements for the table Temp_Num_Table,
> > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
> That's the problem.  I'm in the process of porting a set of about
> 30 Stroed Procedures from MS-SQL server to PostgreSQL and have
> just managed only 3 :-( because of some "This is not possible with
> PostgreSQL".
   Could you try out the current CVS sources?
   I've  added  an  EXECUTE  keyword  to PL/pgSQL earlier - er -   yesterday.  It should give you the power to at least
portthe   proc below to PostgreSQL.
 

> To make clear what I want to do, I just copy the original stored
> procedure, which might be not so clever so that it would be possible
> to go without the table creation:
   Looks  like  the  only purpose of the (temp) table is to hold   exactly one single row of one attribute - the result
of  the   count(*).   From  the  code I guess that MS-SQL's only way to   build up a dynamic query is to put it  as  a
string into  a   variable and call Exec(@var).  Maybe it's impossible to get a   return value out of that  at  the
same time,  so  the  only   solution  is  to  throw  it  into a temp table and pick it up   again.
 
   But anyway, the dynamic part of  building  the  qualification   (WHERE  clause)  was  the initial problem. And that
shouldbe   solved in CURRENT PostgreSQL sources (and  get  shipped  with   7.1).
 
   Read on below...

>
>
> CREATE Procedure TilleA.pHelpCountNames
> ( @Arbeitsgruppeshort varchar(255) ,
>   @Condition          varchar(255)
> )
> /* Count names in table Mitarbeiter which belong to the group
>    Arbeitsgruppeshort and match the condition @Condition
> */
> As
>   Declare @Query varchar(1024)
>   Declare @num int
>   Select  @num = 0
>
>   if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
>     Select @ArbeitsgruppeShort = ''
>   end else begin
>     Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' + @ArbeitsgruppeShort + ''''
>   end
>
>   Create Table #NumTable
>   (
>    Num integer
>   )
>
>   Select @Query =
>          'Insert Into #NumTable (Num) ' +
>          'SELECT Count (*) ' +
>          'FROM Mitarbeiter m ' +
>          'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter   = t.IdMitarbeiter ' +
>          'INNER JOIN Arbeitsgruppe a                ON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' +
>          'WHERE ' + @Condition + @ArbeitsgruppeShort
>   Exec (@Query)
>
>   Select @num=(Select Max(Num) From #NumTable)
>
>   Drop Table #NumTable
>
>   return @num
>
>
> May be there are some clever ways to avoid the temporary table.
> I really wonder if my solution is in fact very clever because I'm unsure
> if it is thread-safe.
>
> Any hints?
   Let me give it a try:
       CREATE FUNCTION pHelpCountNames (text, text) RETURNS integer AS         '           DECLARE             ag_short
    ALIAS FOR $1;             condition     ALIAS FOR $2;             query         text;             rec
record;            result        integer;           BEGIN             query := ''SELECT count(*) AS n FROM Mitarbeiter
m,                              tm2nMistarbeiterArbeitsgruppe t,                               Arbeitsgruppe a
             WHERE m.IdMitarbeiter = t.IdMitarbeiter                          AND t.IdArbeitsgruppe =
a.IdArbeitsgruppe'';
             IF condition != '''' THEN               query := query || '' AND '' || condition;             END IF;
             IF ag_short != '''' THEN               query := query || '' AND a.ArbeitsgruppeShort = '''''' ||
            ag_short || '''''''';             END IF;
 
             FOR rec IN EXECUTE query LOOP               result = rec.n;             END LOOP;
             RETURN result;           END;         ' LANGUAGE 'plpgsql';
   Just written down. Too lazy to test it right now.
   BTW:   It   is  not  strictly  neccessary  to  build  up  the   querystring  for  EXECUTE  in  a  variable.  Any
expression  resulting  in a string (a function call or a concatenation of   strings, variables and functions) is
allowed.
   EXECUTE outside of a FOR statement simply executes the  query   and  that  should  not  be  a  SELECT.  This can be
autility   statement as well, so feel free to  create  and  drop  "temp"   tables as needed. Used as above (inside the
FOR)it must be a   SELECT statement. I haven't checked if  rows  found,  because   that  count(*)  query is guaranteed
toreturn either one row,   or bomb out with a transaction abort. Usually someone  should   check.
 
   Enjoy.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #