Postgresql – Declaring variable for a whole script

postgresqlpostgresql-10

I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions).

I know I can do:

DO $$
DECLARE test float := 0.1 ;
BEGIN
    < do whatever here>
END $$

but that makes me declare the variables each time I have a BEGIN / END block.

Best Answer

There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose.

SET myvars.test TO '0.1';

Persists for the duration of the session (not just transaction). Use with:

SELECT current_setting('myvars.test')::float;

Be aware that values are stored as text. So you may need to cast as demonstrated.

To avoid exceptions when referencing an unset variable and get NULL instead:

SELECT current_setting('myvars.test', true)::float;

See:

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

There are a number of other workarounds, too:

Is there a way to define a named constant in a PostgreSQL query?

Related Solutions

Mysql – Change MySQL backup script to use on PostgreSQL

have a look at pg_dump it's very similar to the dump utility by MySQL See: pg_dump documentation

I think most of the change is just changing the flags to match the new utility and replacing the call for mysql with a similar call to psql -l to get the db list (to filter out dbs you want to ignore)

Postgresql – How to create multiple tables based on existing tables in postgreSQL

Most of these procedural things can be done using a PL/pgSQL DO Block. So you don't have to get into another language. Assuming your main tables have a naming pattern like 'mt_%', you could do something like the following.

do
$create_hist_tables$
declare
  main_table text;
begin
for main_table in
  select
    'public.' || quote_ident(table_name)
  from
    information_schema.tables
  where
    table_name like 'mt_%'
    and table_schema = 'public'
loop
  execute 'CREATE TABLE ' || main_table || '_hist ("action" text, "user" text, like ' || main_table || ' including all)';
end loop;
end
$create_hist_tables$
;

Building a long query using concatenation will not be neat for maintainability. So you could replace the single create query with a series of create and alter commands within the loop.

I have tried this on PG 11.4.

Best Answer

Related Solutions

Mysql – Change MySQL backup script to use on PostgreSQL

Postgresql – How to create multiple tables based on existing tables in postgreSQL

Related Question