Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- https://stackoverflow.com/questions/24618672/function-to-loop-through-and-select-data-from-multiple-tables
- http://okbob.blogspot.fr/2010/12/iteration-over-record-in-plpgsql.html
- CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
- RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
- $func$
- DECLARE
- formal_table text;
- BEGIN
- FOR formal_table IN
- SELECT quote_ident(table_name)
- FROM information_schema.tables
- WHERE table_schema = 'public'
- AND table_name LIKE 'formaltable%'
- LOOP
- RETURN QUERY EXECUTE
- 'SELECT t.natural_id, t.name, t.natural_id_numeric
- FROM internal_idlookup i
- JOIN public.' || formal_table || ' t USING (natural_id_numeric)
- WHERE i.internal_id = 7166571'; -- assuming internal_id is numeric
- END LOOP;
- END
- $func$ LANGUAGE plpgsql;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement