Re: RETURNS SETOF table; language '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(dot)splivalo(at)mobart(dot)hr> wrote in message
news:1131549050(dot)7758(dot)11(dot)camel(at)ekim(dot)(dot)(dot)
> 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(dot)splivalo(at)mobart(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>