Session funtion programming in PostgreSQL's PL/pgSQL

In SQL Server, we can to used:

DECLARE @Variable Type
SELECT * FROM TableName WHERE KeyField = @Variable

like something...

But, PostgreSQL not support SESSION VARIABLE.

However, In PostgreSQL 9.x, it add the statement in PL/pgSQL.

1. DO statement

Example code:
DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;

    DROP TABLE IF EXISTS tmp_table;
    CREATE TABLE tmp_table AS
    SELECT * FROM yourtable WHERE   id = myvar;
END $$;

SELECT * FROM tmp_table;


2. CREATE OR REPLACE FUNCTION

Example code:
CREATE OR REPLACE FUNCTION somefuncname() RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  one int;
  two int;
BEGIN
  one := 1;
  two := 2;
  RETURN one + two;
END
$$;
SELECT somefuncname();


These solution can solve need execute PL/pgSQL in session function.

The Sequelae(後遺症) is remain temp tempFunction / tempTable in PostgreSQL database, when the executed PL/pgSQL command.

See also:
How to declare local variables in postgresql?
PostgreSQL Documentation: SQL-DO
How to declare a variable in a PostgreSQL query

留言

這個網誌中的熱門文章

對於 Delphi 10.1 Berlin 推出的看法

IntraWeb 學習日記:【Login】