Search Postgresql Archives

Re: Planning error in dynamic string creation in plpgsql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux