From: | Keith Fiske <keith(at)omniti(dot)com> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Planning error in dynamic string creation in plpgsql |
Date: | 2014-01-06 02:31:39 |
Message-ID: | CAG1_KcDi6E3tYFZh3w_yypVGHS4sswUKTDPLpQ3e_2Av34V+3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Fiske | 2014-01-06 02:35:53 | Re: Planning error in dynamic string creation in plpgsql |
Previous Message | Erik Darling | 2014-01-05 22:34:37 | Re: help interpreting pg_stat_user_index view values |