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.