File: st_querytables.sql

package info (click to toggle)
postgis 2.3.1%2Bdfsg-2
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 58,660 kB
  • ctags: 10,181
  • sloc: ansic: 132,858; sql: 131,148; xml: 46,460; sh: 4,832; perl: 4,476; makefile: 2,749; python: 1,198; yacc: 442; lex: 131
file content (26 lines) | stat: -rw-r--r-- 1,359 bytes parent folder | download | duplicates (10)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
----------------------------------------------------------------------------------------------------------------------
-- ST_QueryTables
-- Execute a query on a series of table based on a prefix.
-- The string "tablename" will be replaced by the name of the table.
-- schemaname - The schema where to execute the queries.
-- prefix     - Prefix to restraint the query to tables names starting with this string.
-- inquery    - Query to execute. Can contain the 'tablename' string which will be replaced buy the name of the current table.
--
-- Example to drop a set of table
--
-- SELECT ST_QueryTables('public', 'aa', 'DROP TABLE IF EXISTS tablename');
----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_QueryTables(schemaname text, prefix text, inquery text)
RETURNS int AS
$BODY$
DECLARE
    tabletoquery RECORD;
BEGIN
    FOR tabletoquery IN EXECUTE 'SELECT tablename FROM pg_tables WHERE schemaname = ' || quote_literal(schemaname) || ' AND tablename LIKE ' || quote_literal(prefix || '%') LOOP
        RAISE NOTICE 'Querying %', schemaname || '.' || tabletoquery.tablename;
        EXECUTE replace(inquery, 'tablename', '"' || schemaname || '"."' || tabletoquery.tablename || '"');
    END LOOP;
    RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;