RE: Create table in functions

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

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:


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?

Kind regards
         Andreas.