Thread: Helper aggregate function

Hello!<br />Can anyone tell me how i do this properly?<br /><br />create or replace function agg(varchar,varchar)
returnsvoid as $func$<br />        select $1,count(*) from $2 group by $1 order by $1;<br />$func$<br />Language
SQL;<br/><br />Right now this wives me "ERROR:  syntax error at or near "$2" at character 97 <br />select $1,count(*)
from$2 group by $1 order by $1;"<br /><br />Thank you!<br /><br /><br /><br /> 

<span style="font-family: verdana,sans-serif; color: rgb(0, 0, 153);">Please correct me if I am wrong, but as the last
fewlines of the first section in the given document says, you can use $n only for values and not for
identifiers.</span><brstyle="font-family: verdana,sans-serif; color: rgb(0, 0, 153);" /><br style="font-family:
verdana,sans-serif;color: rgb(0, 0, 153);" /><span style="font-family: verdana,sans-serif; color: rgb(0, 0, 153);"><a
href="http://www.postgresql.org/docs/8.3/static/xfunc-sql.html">http://www.postgresql.org/docs/8.3/static/xfunc-sql.html</a></span><br
style="font-family:verdana,sans-serif; color: rgb(0, 0, 153);" /><br style="font-family: verdana,sans-serif; color:
rgb(0,0, 153);" /><span style="font-family: verdana,sans-serif; color: rgb(0, 0, 153);">And one more thing, may be you
wouldwant to use a user-defined return type instead of void for this function.</span><br style="font-family:
verdana,sans-serif;color: rgb(0, 0, 153);" /><br style="font-family: verdana,sans-serif; color: rgb(0, 0, 153);" /><b
style="font-family:verdana,sans-serif; color: rgb(0, 0, 153);">Robins</b><br /><br /><div class="gmail_quote">On Feb 3,
20085:14 PM, sergey kapustin <<a href="mailto:kapustin.sergey@gmail.com">kapustin.sergey@gmail.com</a>> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;">Hello!<br />Can anyone tell me how i do this properly?<br /><br />create or replace function
agg(varchar,varchar)returns void as $func$<br />         select $1,count(*) from $2 group by $1 order by $1;<br
/>$func$<br/>Language SQL;<br /><br />Right now this wives me "ERROR:  syntax error at or near "$2" at character 97 <br
/>select$1,count(*) from $2 group by $1 order by $1;"<br /><br />Thank you!<br /><br /><br /><br
/></blockquote></div><br/> 

hi 

in this case you have to use execute:

>>         select $1,count(*) from $2 group by $1 order by $1;
execute into myResultSet 'select ' || $1 || ' from ' || $2 || ' group by ' || $1 || ' order by  ' || $2; 

see plpgsql docs for detailed information

christian


On Sun, February 3, 2008 19:58, Robins Tharakan wrote:
> Please correct me if I am wrong, but as the last few lines of the first
> section in the given document says, you can use $n only for values and not
> for identifiers.
> 
> http://www.postgresql.org/docs/8.3/static/xfunc-sql.html
> 
> And one more thing, may be you would want to use a user-defined return
> type
> instead of void for this function.
> 
> *Robins*
> 
> On Feb 3, 2008 5:14 PM, sergey kapustin <kapustin.sergey@gmail.com> wrote:
> 
>> Hello!
>> Can anyone tell me how i do this properly?
>>
>> create or replace function agg(varchar,varchar) returns void as $func$
>>         select $1,count(*) from $2 group by $1 order by $1;
>> $func$
>> Language SQL;
>>
>> Right now this wives me "ERROR:  syntax error at or near "$2" at
>> character
>> 97
>> select $1,count(*) from $2 group by $1 order by $1;"
>>
>> Thank you!
>>
>>
>>
>>
> 


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger