Re: RETURNS SETOF table; language 'sql' - Mailing list pgsql-sql

I think its cause you changed your procedure from being written in SQL to 
being writtern in PLPGSQL  in your second implementation....

Sets of records are returned from a PLPGSQL function with a RETURN statement 
... not a SELECT...

Check out the sections of the manual that talk about PLPGSQL....
35.7.1. Returning From a Function
There are two commands available that allow you to return data from a 
function: RETURN and RETURN NEXT.

35.7.1.1. RETURN
RETURN expression;RETURN with an expression terminates the function and 
returns the value of expression to the caller. This form is to be used for 
PL/pgSQL functions that do not return a set.

When returning a scalar type, any expression can be used. The expression's 
result will be automatically cast into the function's return type as 
described for assignments. To return a composite (row) value, you must write 
a record or row variable as the expression.

The return value of a function cannot be left undefined. If control reaches 
the end of the top-level block of the function without hitting a RETURN 
statement, a run-time error will occur.

If you have declared the function to return void, a RETURN statement must 
still be provided; but in this case the expression following RETURN is 
optional and will be ignored if present.

35.7.1.2. RETURN NEXT
RETURN NEXT expression;When a PL/pgSQL function is declared to return SETOF 
sometype, the procedure to follow is slightly different. In that case, the 
individual items to return are specified in RETURN NEXT commands, and then a 
final RETURN command with no argument is used to indicate that the function 
has finished executing. RETURN NEXT can be used with both scalar and 
composite data types; in the latter case, an entire "table" of results will 
be returned.



"Mario Splivalo" <mario.splivalo@mobart.hr> wrote in message 
news:1131549050.7758.11.camel@ekim...
> When I issue something like this:
>
> SELECT * FROM ads WHERE id=1004;
>
> i get:
>
> id  | vpn_id | service_id | ignore_length |       start_time       |
> end_time        |          ad_text
>
------+--------+------------+---------------+------------------------+------------------------+----------------------------
> 1004 |      1 |        106 | f             | 2005-01-01 00:00:00+01 |
> 2005-12-31 00:00:00+01 | Probna reklama numera una!
>
>
> Now, I create a function that does that:
>
> CREATE FUNCTION get_ads(int4)
> RETURNS SETOF ads
> AS
> 'SELECT * FROM ads WHERE id=$1'
> LANGUAGE 'sql'
>
> When I do:
>
> SELECT * FROM get_ads(1004);
>
> i get:
>
> ERROR:  query-specified return row and actual function return row do not
> match
>
> Why is that?
>
> Mike
>
> P.S. That's run on Postgres 7.4.
> -- 
> Mario Splivalo
> Mob-Art
> mario.splivalo@mobart.hr
>
> "I can do it quick, I can do it cheap, I can do it well. Pick any two."
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>