Re: Planning error in dynamic string creation in plpgsql

Sorry, forgot to include that I've tested this on PostgreSQL versions 9.2.6
and 9.3.2 and same thing happens on both.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

On Sun, Jan 5, 2014 at 9:31 PM, Keith Fiske <keith(at)omniti(dot)com> 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.
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>