Skip to content

Instantly share code, notes, and snippets.

@chochos
Created July 22, 2019 17:39
Show Gist options
  • Save chochos/d9042135eaf73cf321720802a83eca68 to your computer and use it in GitHub Desktop.
Save chochos/d9042135eaf73cf321720802a83eca68 to your computer and use it in GitHub Desktop.
Using a table function instead of a view.
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 'CREATE FUNCTION bar_function() RETURNS TABLE(' ||
columns || ') AS $$ BEGIN' ||
' RETURN QUERY SELECT * 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

Instead of creating a view, this example shows how to create a table function that returns the same structure that a view would have; to do this we need to create the function dynamically, from another function.

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