Created
July 22, 2019 18:05
-
-
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.