RE: PL/PGSQL function with parameters - Mailing list pgsql-sql

<p><font size="2">Just for the record:</font><p><font size="2">DROP FUNCTION table_count(varchar);</font><br /><font
size="2">CREATEFUNCTION table_count(varchar) RETURNS integer AS '</font><br /><font size="2">DECLARE</font><br
/>       <font size="2">SQL varchar;</font><br />        <font size="2">RES integer;</font><br /><font
size="2">BEGIN</font><br/>        <font size="2">SQL = ''SELECT * INTO temp1 FROM '' || $1;</font><br />        <font
size="2">EXECUTESQL;</font><br />        <font size="2">SELECT count(*) INTO RES FROM temp1;</font><br />        <font
size="2">RETURN(RES)</font><br/><font size="2">END;</font><br /><font size="2">'</font><br /><font size="2">LANGUAGE
'plpgsql';</font><p><fontsize="2">...</font><br /><p><font size="2">dev=> select table_count('switch');</font><br
/><fontsize="2"> test</font><br /><font size="2">------</font><br /><font size="2">    6</font><br /><font size="2">(1
row)</font><br/><p><font size="2">This function produces exactly what you would hope for, a count of rows in the
specifiedtable.  It's particularly inefficient at doing it, because it does a table copy (and doesn't bother to clean
upafter itself ;-(), so don't do this on a large table ;-) but it shows the principle.</font><p><font size="2">What I
couldn'tget it to do was to select directly into the variable RES.  Perhaps someone could enlighten me.</font><p><font
size="2">Cheers...</font><br/><p><font size="2">MikeA</font><p><font size="2">-----Original Message-----</font><br
/><fontsize="2">From: David Richter [<a
href="mailto:D.Richter@DKFZ-heidelberg.de">mailto:D.Richter@DKFZ-heidelberg.de</a>]</font><br/><font size="2">Sent: 06
February2001 09:39</font><br /><font size="2">To: Michael Ansley</font><br /><font size="2">Subject: Re: [SQL] PL/PGSQL
functionwith parameters</font><br /><p><font size="2">Hello!</font><p><font size="2">Thanks a lot for Your
answer!</font><p><fontsize="2">But with my version 7.0.2. this suggestion doesn't work:</font><p><font size="2">It
appears:parser: parse error at or near "exec" or </font><br /><font size="2">parser: parse error at or near
"execute"</font><p><fontsize="2">And how should i design the update command in the suggested way e.g.?</font><p><font
size="2">EXEC''UPDATE '' ||$1 </font><br /><font size="2">                      ''SET '' || $2 '' =
psr_rec.parentoid</font><br/><font size="2">                        WHERE chilioid = psr_rec.childoid;''</font><br
/><p><fontsize="2">Wich exact release I will need to use this feature?</font><br /><font size="2">Wich one are You
using?</font><p><fontsize="2">Greetings</font><p><font size="2">David</font><code><font size="3"><br /><br />
**********************************************************************<br/> This email and any files transmitted with
itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed.
Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This
footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer
viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br
/></font></code>