Postgres does not normally use variables in plain SQL. But you can do that, too: SET foo.test=”SELECT bar FROM baz”; SELECT current_setting(‘foo.test’); Read about Customized Options in the manual. In PostgreSQL 9.1 or earlier you needed to declare custom_variable_classes before you could use that. However, You cannot EXECUTE dynamic SQL without a PL (procedural language). … Read more
This construct is not possible: IF EXECUTE ‘EXISTS (SELECT 1 FROM mytable)’ THEN … You can simplify to: IF EXISTS (SELECT 1 FROM mytable) THEN … But your example is probably simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check the special variable FOUND immediately after executing any DML command … Read more
A simpler way has been around since PostgreSQL 8.3: CREATE FUNCTION test() RETURNS SETOF first_table AS $func$ BEGIN RETURN QUERY SELECT * FROM first_table; RETURN QUERY SELECT * FROM second_table; — has to return same rowtype as first_table! END $func$ LANGUAGE plpgsql; Call: SELECT * FROM test(); Both result sets are appended to a single … Read more
Faced the problem a few years ago, and used pg2mysql EDIT 21 july 2019 : this fork seems a better option (patches, light maintenance)
Yes you can. There are a number of ways. 1) RETURN (SELECT …) CREATE OR REPLACE FUNCTION get_1(_param_id integer) RETURNS integer LANGUAGE plpgsql AS $func$ BEGIN RETURN _param_id; — Or: — RETURN (SELECT col1 FROM tbl WHERE id = _param_id); END $func$; 2) Use an OUT or INOUT parameter CREATE OR REPLACE FUNCTION get_2(_param_id integer, … Read more
The error you quote has nothing to do with pg_hba.conf; it’s failing to connect, not failing to authorize the connection. Do what the error message says: Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections You haven’t shown the command that produces the error. Assuming you’re connecting on … Read more
There is a very good tool that imports tables into Postgres from a csv file. It is a command-line tool called pgfutter (with binaries for windows, linux, etc.). One of its big advantages is that it recognizes the attribute/column names as well. The usage of the tool is simple. For example if you’d like to … Read more
You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2. create or replace function refresh_mat_view() returns trigger language plpgsql as $$ begin refresh materialized view mat_view; return null; end $$; create trigger refresh_mat_view after insert or update or delete or truncate on table1 for each statement execute procedure refresh_mat_view(); … Read more
You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies: (but note that ALL TABLES is considered to include views and foreign tables). Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual: For sequences, this privilege allows … Read more
For those who are interested, here is the solution I came up with, inspired from Craig Ringer‘s comment: (…) use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones.(…) The chosen solution comes down like this: First, we upgrade to Postgresql 9.2. Then, … Read more