2014/1/6 Keith Fiske <ke...@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.kla...@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.kla...@gmail.com >> > >
