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