Re: Planning error in dynamic string creation in plpgsql - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Planning error in dynamic string creation in plpgsql |
Date | |
Msg-id | 52CA2BBE.4020405@gmail.com Whole thread Raw |
In response to | Planning error in dynamic string creation in plpgsql (Keith Fiske <keith@omniti.com>) |
Responses |
Re: Planning error in dynamic string creation in plpgsql
Re: Planning error in dynamic string creation in plpgsql |
List | pgsql-general |
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@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.klaver@gmail.com
pgsql-general by date: