Re: Planning error in dynamic string creation in plpgsql

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.

--
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
>