Обсуждение: Planning error in dynamic string creation in plpgsql

Поиск
Список
Период
Сортировка

Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
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

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
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@omniti.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

Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
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

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
That fixed it! In the example and my original as well. Thank you very much!

And wow that was incredibly misleading where the cast was supposed to go going by the error given and when it was thrown. That EXECUTE statement works perfectly fine, seeing as the v_record variable got its assignment with no issue.

Any chance you can explain what's going on here? Never would've thought to put the cast there to fix the problem.

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


On Sun, Jan 5, 2014 at 11:06 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
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

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
Actually, that doesn't work right. Gives weird results when the column is an integer

Example:

keith=# select min(col1), max(col1) from partman_test.time_static_table_p2014_01_01;
 min | max
-----+-----
  86 | 100
(1 row)

keith=# select min(col1::text), max(col1::text) from partman_test.time_static_table_p2014_01_01;
 min | max
-----+-----
 100 | 99
(1 row)



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


On Sun, Jan 5, 2014 at 11:23 PM, Keith Fiske <keith@omniti.com> wrote:
That fixed it! In the example and my original as well. Thank you very much!

And wow that was incredibly misleading where the cast was supposed to go going by the error given and when it was thrown. That EXECUTE statement works perfectly fine, seeing as the v_record variable got its assignment with no issue.

Any chance you can explain what's going on here? Never would've thought to put the cast there to fix the problem.

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


On Sun, Jan 5, 2014 at 11:06 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
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


Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
On 01/05/2014 08:23 PM, Keith Fiske wrote:
> That fixed it! In the example and my original as well. Thank you very much!
>
> And wow that was incredibly misleading where the cast was supposed to go
> going by the error given and when it was thrown. That EXECUTE statement
> works perfectly fine, seeing as the v_record variable got its assignment
> with no issue.
>
> Any chance you can explain what's going on here? Never would've thought
> to put the cast there to fix the problem.

No absolute answer, just educated guesses. One, the EXECUTE caused the
v_record to be dynamically planned for each pass so it dealt with the
type change. The assignment to v_sql in the FOREACH was planned the
first time through. On the second pass when the type changed it choked.

Two from here:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

' If the expression's result data type doesn't match the variable's data
type, or the variable has a specific size/precision (like char(20)), the
result value will be implicitly converted by the PL/pgSQL interpreter
using the result type's output-function and the variable type's
input-function. Note that this could potentially result in run-time
errors generated by the input function, if the string form of the result
value is not acceptable to the input function.'

Led to the hunch that casting to text in the query would be the key.

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



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
On 01/05/2014 08:34 PM, Keith Fiske wrote:
> Actually, that doesn't work right. Gives weird results when the column
> is an integer
>
> Example:
>
> keith=# select min(col1), max(col1) from
> partman_test.time_static_table_p2014_01_01;
>   min | max
> -----+-----
>    86 | 100
> (1 row)
>
> keith=# select min(col1::text), max(col1::text) from
> partman_test.time_static_table_p2014_01_01;
>   min | max
> -----+-----
>   100 | 99
> (1 row)
>

Because it is working on a string in the second case. In the function
remove the quote_literal() and see if that works.

>
>



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
I can't remove the quote_literal() because the value could potentially be a string, time, or number. Without the loop, quote_literal() handles the variable being any one of those types without any issues and quotes (or doesn't) as needed.

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


On Sun, Jan 5, 2014 at 11:40 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/05/2014 08:34 PM, Keith Fiske wrote:
Actually, that doesn't work right. Gives weird results when the column
is an integer

Example:

keith=# select min(col1), max(col1) from
partman_test.time_static_table_p2014_01_01;
  min | max
-----+-----
   86 | 100
(1 row)

keith=# select min(col1::text), max(col1::text) from
partman_test.time_static_table_p2014_01_01;
  min | max
-----+-----
  100 | 99
(1 row)


Because it is working on a string in the second case. In the function remove the quote_literal() and see if that works.






--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
On 01/05/2014 08:34 PM, Keith Fiske wrote:
> Actually, that doesn't work right. Gives weird results when the column
> is an integer
>
> Example:
>
> keith=# select min(col1), max(col1) from
> partman_test.time_static_table_p2014_01_01;
>   min | max
> -----+-----
>    86 | 100
> (1 row)
>
> keith=# select min(col1::text), max(col1::text) from
> partman_test.time_static_table_p2014_01_01;
>   min | max
> -----+-----
>   100 | 99
> (1 row)


Should have added to my previous post, that when I want to deal with
truly dynamic values I use plpythonu. plpgsql is very useful but it does
not like types changing under it. plpythonu deals with that better.

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


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
On 01/05/2014 08:43 PM, Keith Fiske wrote:
> I can't remove the quote_literal() because the value could potentially
> be a string, time, or number. Without the loop, quote_literal() handles
> the variable being any one of those types without any issues and quotes
> (or doesn't) as needed.

Well I tried it in your example function and it seemed to work. I don't
think it is quote_literal that is the problem, it is the assignment to
v_sql. In the FOREACH it seems v_sql is not being replanned. In the
other loop there two instances of it and they segregated by type at the
start so they do not suffer the planning issue with types.

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


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Pavel Stehule
Дата:



2014/1/6 Keith Fiske <keith@omniti.com>
That fixed it! In the example and my original as well. Thank you very much!

And wow that was incredibly misleading where the cast was supposed to go going by the error given and when it was thrown. That EXECUTE statement works perfectly fine, seeing as the v_record variable got its assignment with no issue.

Any chance you can explain what's going on here? Never would've thought to put the cast there to fix the problem.

Some plpgsql statements has stored prepared plans with fixed used types. A assign statement is not a exception. When these plans are reused, then you have to use same types.

Regards

Pavel
 

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


On Sun, Jan 5, 2014 at 11:06 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
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


Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
That is not an option either. This is for a publicly released extension and I'm really not going to go requiring another scripting language be installed, especially an untrusted one.


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


On Sun, Jan 5, 2014 at 11:44 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/05/2014 08:34 PM, Keith Fiske wrote:
Actually, that doesn't work right. Gives weird results when the column
is an integer

Example:

keith=# select min(col1), max(col1) from
partman_test.time_static_table_p2014_01_01;
  min | max
-----+-----
   86 | 100
(1 row)

keith=# select min(col1::text), max(col1::text) from
partman_test.time_static_table_p2014_01_01;
  min | max
-----+-----
  100 | 99
(1 row)


Should have added to my previous post, that when I want to deal with truly dynamic values I use plpythonu. plpgsql is very useful but it does not like types changing under it. plpythonu deals with that better.





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


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
David Johnston
Дата:
Adrian Klaver-3 wrote
>>
>>
>> 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;

Right idea if the loss of original type in the min/max query is acceptable
(which it should be).  But the cast wants to be of the min/max RESULT not
the min/max INPUT.

SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text AS
max_text FROM ...

Min/Max logic wants to be done by the rules for the original type, not
according to string collation rules.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Planning-error-in-dynamic-string-creation-in-plpgsql-tp5785421p5785523.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Planning error in dynamic string creation in plpgsql

От
Adrian Klaver
Дата:
On 01/06/2014 07:42 AM, David Johnston wrote:
> Adrian Klaver-3 wrote
>>>
>>>
>>> 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;
>
> Right idea if the loss of original type in the min/max query is acceptable
> (which it should be).  But the cast wants to be of the min/max RESULT not
> the min/max INPUT.
>
> SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text AS
> max_text FROM ...
>
> Min/Max logic wants to be done by the rules for the original type, not
> according to string collation rules.

Yes, that would be the way to do it.

>
> David J.
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
David,

That seems to have fixed it! I was going down a path of grabbing the column's type from pg_attribute and trying to work from there, but was still having some of the same issues.

Thanks everyone else that replied as well!

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


On Mon, Jan 6, 2014 at 10:42 AM, David Johnston <polobo@yahoo.com> wrote:
Adrian Klaver-3 wrote
>>
>>
>> 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;

Right idea if the loss of original type in the min/max query is acceptable
(which it should be).  But the cast wants to be of the min/max RESULT not
the min/max INPUT.

SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text AS
max_text FROM ...

Min/Max logic wants to be done by the rules for the original type, not
according to string collation rules.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Planning-error-in-dynamic-string-creation-in-plpgsql-tp5785421p5785523.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: Planning error in dynamic string creation in plpgsql

От
Keith Fiske
Дата:
Just wanted to say thanks again for the help to those that responded. For anyone curious, this helped me get a more advanced constraint exclusion feature finished for the partition manager I've been working on

http://www.keithf4.com/managing-constraint-exclusion-in-table-partitioning/

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


On Mon, Jan 6, 2014 at 11:33 AM, Keith Fiske <keith@omniti.com> wrote:
David,

That seems to have fixed it! I was going down a path of grabbing the column's type from pg_attribute and trying to work from there, but was still having some of the same issues.

Thanks everyone else that replied as well!

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


On Mon, Jan 6, 2014 at 10:42 AM, David Johnston <polobo@yahoo.com> wrote:
Adrian Klaver-3 wrote
>>
>>
>> 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;

Right idea if the loss of original type in the min/max query is acceptable
(which it should be).  But the cast wants to be of the min/max RESULT not
the min/max INPUT.

SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text AS
max_text FROM ...

Min/Max logic wants to be done by the rules for the original type, not
according to string collation rules.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Planning-error-in-dynamic-string-creation-in-plpgsql-tp5785421p5785523.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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