RE: PL/PGSQL function with parameters

Just for the record:

DROP FUNCTION table_count(varchar);
CREATE FUNCTION table_count(varchar) RETURNS integer AS '
DECLARE
SQL varchar;
RES integer;
BEGIN
SQL = ''SELECT * INTO temp1 FROM '' || $1;
EXECUTE SQL;
SELECT count(*) INTO RES FROM temp1;
RETURN(RES)
END;
'
LANGUAGE 'plpgsql';

...

dev=> select table_count('switch');
test
------
6
(1 row)

This function produces exactly what you would hope for, a count of rows in
the specified table. It's particularly inefficient at doing it, because it
does a table copy (and doesn't bother to clean up after itself ;-(), so
don't do this on a large table ;-) but it shows the principle.

What I couldn't get it to do was to select directly into the variable RES.
Perhaps someone could enlighten me.

Cheers...

MikeA

-----Original Message-----
From: David Richter [mailto:D(dot)Richter(at)DKFZ-heidelberg(dot)de]
Sent: 06 February 2001 09:39
To: Michael Ansley
Subject: Re: [SQL] PL/PGSQL function with parameters

Hello!

Thanks a lot for Your answer!

But with my version 7.0.2. this suggestion doesn't work:

It appears: parser: parse error at or near "exec" or
parser: parse error at or near "execute"

And how should i design the update command in the suggested way e.g.?

EXEC ''UPDATE '' ||$1
''SET '' || $2 '' = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;''

Wich exact release I will need to use this feature?
Wich one are You using?

Greetings

David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************