Re: proposal: new contrib module plpgsql's embeded sql validator

2011/7/22 Jim Nasby <jim(at)nasby(dot)net>:
> On Jul 19, 2011, at 10:51 PM, Pavel Stehule wrote:
>>> If you mean that such checks would be done automatically, no, they
>>> shouldn't be.  Consider a function that creates a table and then uses
>>> it, or even just depends on using a table that doesn't yet exist when
>>> you do CREATE FUNCTION.
>>
>> yes, any deep check is not possible for function that uses a temporary tables.
>>
>> A plpgsql_lint is not silver bullet - for these cases is necessary to
>> disable lint.
>>
>> . I can't to speak generally - I have no idea, how much percent of
>> functions are functions with access to temporary tables - in my last
>> project I use 0 temp tables on cca 300 KB of plpgsql code.
>>
>> The more terrible problem is a new dependency between functions. I use
>> a workaround - some like headers
>
> You can work around temp table issues the same way: just define the temp table before you create the function.
>
> In practice, if I have a function that depends on a temp table it either creates it itself if it doesn't already exist or I have a separate function to create the table; that way you have a single place that has the temp table definition, and that is in the database itself.

there is other trick - use a persistent table with same name before.
Runtime temporary table is near in search_path, so all executed SQL
will be related to this temp table.

Pavel

> --
> Jim C. Nasby, Database Architect                   jim(at)nasby(dot)net
> 512.569.9461 (cell)                         http://jim.nasby.net
>
>
>