clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926612 fiddles created (21862 in the last week).

CREATE TABLE nominal_dollars (year int, gdp int, dividends int); INSERT INTO nominal_dollars VALUES (2000,100,20) , (2001,110,30) , (2002,120,40); CREATE TABLE cpi(year int, cpi_adjustment int); INSERT INTO cpi VALUES (2000, 1) , (2001, 2) , (2002, 3); CREATE OR REPLACE FUNCTION f_results_inflation_adjusted(_tbl text) RETURNS void LANGUAGE plpgsql AS $func$ BEGIN -- RAISE NOTICE '%', ( -- use instead of EXECUTE for debugging EXECUTE ( SELECT format('DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS SELECT %3$s FROM %2$I t JOIN cpi c USING (year)' , _tbl || '_adj' -- %1 , _tbl -- %2 , string_agg( -- %3 CASE a.attname WHEN 'id' THEN 't.id' -- case sensitive! WHEN 'year' THEN 't.year' ELSE format('t.%1$I * c.cpi_adjustment AS %1$I', a.attname) END, ', ' ORDER BY a.attnum ) ) FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON c.oid = a.attrelid WHERE c.relname = _tbl AND c.relnamespace = current_user::regnamespace -- adapted for dbfiddle! AND NOT a.attisdropped AND a.attnum > 0 ); END $func$;
3 rows affected
3 rows affected
 hidden batch(es)


TABLE nominal_dollars;
year gdp dividends
2000 100 20
2001 110 30
2002 120 40
 hidden batch(es)


SELECT f_results_inflation_adjusted('nominal_dollars');
ERROR: schema "u_1564413057" does not exist CONTEXT: SQL statement "SELECT ( SELECT format('DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS SELECT %3$s FROM %2$I t JOIN cpi c USING (year)' , _tbl || '_adj' -- %1 , _tbl -- %2 , string_agg( -- %3 CASE a.attname WHEN 'id' THEN 't.id' -- case sensitive! WHEN 'year' THEN 't.year' ELSE format('t.%1$I * c.cpi_adjustment AS %1$I', a.attname) END, ', ' ORDER BY a.attnum ) ) FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON c.oid = a.attrelid WHERE c.relname = _tbl AND c.relnamespace = current_user::regnamespace -- adapted for dbfiddle! AND NOT a.attisdropped AND a.attnum > 0 )" PL/pgSQL function f_results_inflation_adjusted(text) line 4 at EXECUTE
 hidden batch(es)


TABLE nominal_dollars_adj;
ERROR: relation "nominal_dollars_adj" does not exist LINE 1: TABLE nominal_dollars_adj; ^
 hidden batch(es)