Stored procedures

Hi,
I am having problems with a stored procedure (plpgsql) that takes in a  
value and returns a record set.
my code is as follow:

create function pg_clientRec(text) setof record as
'
declare
customerID ALIAS $1;
rec record;

begin
	select into rec * from troubletickets where custID = customerID;
	return rec;
end
'
language 'plpgsql';

I am calling the procedure as follows:

select clientRec('tmpg60');

I am getting the following error:

ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/ 
pgSQL function "pg_clientRec" while casting return value to function's r 
eturn type

What am I doing wrong????

Re: Stored procedures

beyaRecords - The home Urban music <uzo <at> beya-records.com> writes:
> I am having problems with a stored procedure (plpgsql) that takes in a
> value and returns a record set.
> I am calling the procedure as follows:
> select clientRec('tmpg60');

Use 

	select * from clientRec('tmpg60') as (column list);

If you declare the function as returning RECORD, you will need to
provide an AS clause that identifies the column set the records will
contain.  Without this, the parser has no idea what to expand "*" to.
See the example in section 7.2.1.4 here:
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly

Ambiguous error message

In PostgreSQL 7.4, the following select:

select texten, total
from (select protocolid, count(*) as total) from ips where catid=1
      group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

gives the error message:

ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

Why isn't the "as c" considered as an alias?

  Sam
--

-- 
Samuel Tardieu -- sam <at> rfc1149.net -- http://www.rfc1149.net/sam

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)

Re: Ambiguous error message

Hi Sam,

I'm not quite sure what you want the query to return, but you've got a 
problem with your parentheses. You've got two FROM clauses and an INNER 
JOIN, which together aren't arranged properly. I've rearranged your 
query a little, but I haven't changed anything. Perhaps this'll make it 
a little clearer to you.

On Jan 2, 2004, at 10:49 AM, Samuel Tardieu wrote:

> In PostgreSQL 7.4, the following select:
>
> select texten, total
> from (select protocolid, count(*) as total)
> from ips
> where catid=1
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);

  If you explain a little more what your query is trying to return, I 
might be able to help more.

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Re: Ambiguous error message

Samuel Tardieu <sam <at> rfc1149.net> writes:
> In PostgreSQL 7.4, the following select:
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);

> gives the error message:

> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.

> Why isn't the "as c" considered as an alias?

It's complaining about this part:

from (select protocolid, count(*) as total) from ips where
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It hasn't gotten as far as noticing your mismatched parentheses and
two top-level FROM keywords yet .

It would probably be helpful if this message included a cursor location...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Ambiguous error message

On  2/01, Michael Glaesemann wrote:

| I'm not quite sure what you want the query to return, but you've got a 
| problem with your parentheses.

I can't believe this :) I got hit by the "inner query needs to be aliased"
message and added it to the wrong place and them munged the query a lot.

The correct one was of course:
# select texten, total from (select protocolid, count(*) as total from ips
  where catid=1 group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

(PostgreSQL is used for the will-spam-for-food black-list, see
 http://www.rfc1149.net/wsff)

   Sam

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)

Re: Ambiguous error message

On  3/01, Samuel Tardieu wrote:

| On  2/01, Michael Glaesemann wrote:
| 
| | I'm not quite sure what you want the query to return, but you've got a 
| | problem with your parentheses.
| 
| I can't believe this :) I got hit by the "inner query needs to be aliased"
| message and added it to the wrong place and them munged the query a lot.
| 
| The correct one was of course:
| # select texten, total from (select protocolid, count(*) as total from ips
|   where catid=1 group by protocolid order by protocolid) as c
|   inner join protocols using (protocolid);

(with the "order by" part moved at the end of the query to avoid losing the
order during the inner join)

  Sam

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Re: Ambiguous error message

On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> In PostgreSQL 7.4, the following select:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?

The query has a right parenthesis after "as total" that looks like
it shouldn't be there.

--

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

sort by on two columns

Re: Ambiguous error message

On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
                                            ^
                                            +--- There
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?

	That's maybe due to the highlighted spurious parenthesis.

--

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html