Re: Planning error in dynamic string creation in plpgsql

2014/1/6 Keith Fiske <keith(at)omniti(dot)com>

> That fixed it! In the example and my original as well. Thank you very much!
>
> And wow that was incredibly misleading where the cast was supposed to go
> going by the error given and when it was thrown. That EXECUTE statement
> works perfectly fine, seeing as the v_record variable got its assignment
> with no issue.
>
> Any chance you can explain what's going on here? Never would've thought to
> put the cast there to fix the problem.
>

Some plpgsql statements has stored prepared plans with fixed used types. A
assign statement is not a exception. When these plans are reused, then you
have to use same types.

Regards

Pavel

>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>
>
> On Sun, Jan 5, 2014 at 11:06 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:
>
>> On 01/05/2014 06:31 PM, Keith Fiske wrote:
>>
>>> Running into an issue trying to dynamically create some SQL statements
>>> in a plpgsql function. The function below is as simple an example I can
>>> make to reproduce the error. The first loop works without any issues,
>>> but the second throws an error.
>>>
>>> CREATE OR REPLACE FUNCTION testing_record() RETURNS void
>>> LANGUAGE plpgsql
>>> AS $$
>>> DECLARE
>>> v_col text;
>>> v_col_names text[];
>>> v_record record;
>>> v_sql text;
>>> BEGIN
>>>
>>> CREATE TEMP TABLE test_temp (col1 int, col2 text, col3 timestamp);
>>> INSERT INTO test_temp VALUES (1, 'stuff', now());
>>> INSERT INTO test_temp VALUES (2, 'stuff', CURRENT_TIMESTAMP + '1
>>> day'::interval);
>>>
>>> v_col_names := '{"col1","col3"}';
>>>
>>> FOR i IN 1..2
>>> LOOP
>>> IF i = 1 THEN
>>> EXECUTE 'SELECT min(col1) as min, max(col1) as max FROM
>>> test_temp' INTO v_record;
>>> RAISE NOTICE 'v_record: %', v_record;
>>> v_sql := concat('col1 min: ', quote_literal(v_record.min), ',
>>> col1 max: ', quote_literal(v_record.max) );
>>> RAISE NOTICE 'v_sql: %', v_sql;
>>> ELSIF i = 2 THEN
>>> EXECUTE 'SELECT min(col3) as min, max(col3) as max FROM
>>> test_temp' INTO v_record;
>>> RAISE NOTICE 'v_record: %', v_record;
>>> v_sql := concat('col3 min: ', quote_literal(v_record.min), ',
>>> col3 max: ', quote_literal(v_record.max) );
>>> RAISE NOTICE 'v_sql: %', v_sql;
>>> END IF;
>>> END LOOP;
>>>
>>> FOREACH v_col IN ARRAY v_col_names
>>> LOOP
>>> EXECUTE 'SELECT min('||v_col||') as min, max('||v_col||') as max
>>> FROM test_temp' INTO v_record;
>>> RAISE NOTICE 'v_record: %', v_record;
>>> v_sql := concat(v_col
>>> , ' min: '
>>> , quote_literal(v_record.min)
>>> , ', '
>>> , v_col
>>> , ' max: '
>>> , quote_literal(v_record.max)
>>> );
>>> RAISE NOTICE 'v_sql: %', v_sql;
>>> END LOOP;
>>>
>>> DROP TABLE IF EXISTS test_temp;
>>>
>>> END
>>> $$;
>>>
>>> keith=# select testing_record();
>>> NOTICE: v_record: (1,2)
>>> NOTICE: v_sql: col1 min: '1', col1 max: '2'
>>> NOTICE: v_record: ("2014-01-05 21:24:21.039656","2014-01-06
>>> 21:24:21.039656")
>>> NOTICE: v_sql: col3 min: '2014-01-05 21:24:21.039656', col3 max:
>>> '2014-01-06 21:24:21.039656'
>>> NOTICE: v_record: (1,2)
>>> NOTICE: v_sql: col1 min: '1', col1 max: '2'
>>> NOTICE: v_record: ("2014-01-05 21:24:21.039656","2014-01-06
>>> 21:24:21.039656")
>>> ERROR: type of parameter 7 (timestamp without time zone) does not match
>>> that when preparing the plan (integer)
>>> CONTEXT: PL/pgSQL function testing_record() line 34 at assignment
>>>
>>>
>>> I've narrowed down the exact point of the error being the
>>> quote_literal() calls. If I commend them out like this:
>>>
>>> v_sql := concat(v_col
>>> , ' min: '
>>> -- , quote_literal(v_record.min)
>>> , ', '
>>> , v_col
>>> , ' max: '
>>> -- , quote_literal(v_record.max)
>>> );
>>>
>>> Then the function runs without any issues, but obviously the values are
>>> missing from the NOTICE
>>>
>>> keith=# select testing_record();
>>> NOTICE: v_record: (1,2)
>>> NOTICE: v_sql: col1 min: '1', col1 max: '2'
>>> NOTICE: v_record: ("2014-01-05 21:25:58.603149","2014-01-06
>>> 21:25:58.603149")
>>> NOTICE: v_sql: col3 min: '2014-01-05 21:25:58.603149', col3 max:
>>> '2014-01-06 21:25:58.603149'
>>> NOTICE: v_record: (1,2)
>>> NOTICE: v_sql: col1 min: , col1 max:
>>> NOTICE: v_record: ("2014-01-05 21:25:58.603149","2014-01-06
>>> 21:25:58.603149")
>>> NOTICE: v_sql: col3 min: , col3 max:
>>> testing_record
>>> ----------------
>>>
>>> (1 row)
>>>
>>>
>>> In the real function I'm writing, the columns to be used in the string
>>> being created are pulled from a configuration table, so their types
>>> could be anything. So casting the quote_literal() calls is not really an
>>> option here.
>>>
>>> Any help would be appreciated.
>>>
>>
>> Cast before the quote_literal?
>>
>> Example:
>>
>> EXECUTE 'SELECT min('||v_col||'::text) as min, max('||v_col||'::text) as
>> max FROM test_temp' INTO v_record;
>>
>> postgres(at)test=# select testing_record();
>>
>> NOTICE: v_record: (1,2)
>> NOTICE: v_sql: col1 min: '1', col1 max: '2'
>> NOTICE: v_record: ("2014-01-05 20:02:40.387425","2014-01-06
>> 20:02:40.387425")
>> NOTICE: v_sql: col3 min: '2014-01-05 20:02:40.387425', col3 max:
>> '2014-01-06 20:02:40.387425'
>>
>> NOTICE: v_record: (1,2)
>> NOTICE: v_sql: col1 min: '1', col1 max: '2'
>> NOTICE: v_record: ("2014-01-05 20:02:40.387425","2014-01-06
>> 20:02:40.387425")
>> NOTICE: v_sql: col3 min: '2014-01-05 20:02:40.387425', col3 max:
>> '2014-01-06 20:02:40.387425'
>> testing_record
>> ----------------
>>
>>
>>
>>> --
>>> Keith Fiske
>>> Database Administrator
>>> OmniTI Computer Consulting, Inc.
>>> http://www.keithf4.com
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)com
>>
>
>