Skip to content

Instantly share code, notes, and snippets.

@chochos
Created July 22, 2019 18:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save chochos/2403aaab53e58219f3ba4394e3ab7626 to your computer and use it in GitHub Desktop.
Save chochos/2403aaab53e58219f3ba4394e3ab7626 to your computer and use it in GitHub Desktop.
Version 2 of using a table function instead of a view, this time no errors are thrown anywhere.
CREATE TABLE foo(
some_id SERIAL PRIMARY KEY,
another INT,
stuff VARCHAR(20)
);
CREATE OR REPLACE FUNCTION insert_crap_into_foo() RETURNS INTERVAL AS
$$
DECLARE
counter INT := 0;
start TIMESTAMP := timeOfDay();
stop TIMESTAMP;
BEGIN
LOOP
counter := counter + 1;
INSERT INTO foo(another) VALUES(counter);
EXIT WHEN counter > 2000000;
END LOOP;
stop := timeOfDay();
RETURN stop - start;
END
$$
language plpgsql;
SELECT insert_crap_into_foo();
SELECT now() AS creating_view;
CREATE OR REPLACE FUNCTION create_bar_function() RETURNS INT AS
$META$
DECLARE
columns TEXT;
query TEXT;
cmd TEXT;
BEGIN
SELECT (select string_agg(column_name || ' ' || data_type, ', ')
from information_schema.columns where table_name = 'foo'
group by table_name) INTO columns;
SELECT (select string_agg('CAST(foo.' || column_name || ' AS ' || data_type, '), ')
from information_schema.columns where table_name = 'foo'
group by table_name) INTO query;
SELECT 'CREATE FUNCTION bar_function() RETURNS TABLE(' ||
columns || ') AS $$ BEGIN' ||
' RETURN QUERY SELECT ' || query || ') FROM foo;' ||
' END $$ language plpgsql;'
INTO cmd;
EXECUTE 'DROP FUNCTION IF EXISTS bar_function();';
EXECUTE cmd;
RETURN 1;
END
$META$
language plpgsql;
SELECT create_bar_function();
SELECT * FROM bar_function() LIMIT 10;
ALTER TABLE foo ADD more_stuff NUMERIC(10,2);
ALTER TABLE foo DROP more_stuff;
ALTER TABLE foo ALTER COLUMN stuff TYPE VARCHAR(50);
ALTER TABLE foo ALTER COLUMN another TYPE BIGINT;
SELECT * FROM bar_function() LIMIT 10; --error
SELECT create_bar_function();
SELECT * FROM bar_function() LIMIT 10; --OK
DROP FUNCTION insert_crap_into_foo();
DROP FUNCTION bar_function();
DROP FUNCTION create_bar_function();
DROP TABLE foo;
@chochos
Copy link
Author

chochos commented Jul 22, 2019

By casting each column to its current type in the query inside the table function, the table can later be modified and the function doesn't stop working (it will return the old types though). The table function can be re-created at any time and it will start returning the new types.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment