Re: Using Temporary Tables in postgres functions

On Thu, 25 Jan 2007, Mario Splivalo wrote:

> When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
> as a function language), I can't because postgres can't find that
> temporary table. Consider this example:
>
> CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$
> CREATE TEMPORARY TABLE tmpTbl
> AS
> SELECT
> message_id
> FROM
> cached_messages
> WHERE
> billing_status = 2;
>
>
> UPDATE cached_messages SET billing_status = 1 WHERE message_id IN
> (SELECT message_id FROM tmpTbl);
>
> SELECT
> *
> FROM
> v_messages_full
> WHERE
> message_id IN (SELECT message_id FROM tmpTbl);
> $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER;

It seems like the sql function checker is unhappy with the above. Does it
actually work if you turn off the check_function_bodies configuration
variable, create the function and then call it?