Re: RE: Create table in functions

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 port the
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 should be
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 a utility
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 to return 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(at)Yahoo(dot)com #