2014/01/22

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

沒有留言:

張貼留言

How to use URLEncode in Delphi

URL編碼,也稱【百分號編碼(Percent-encoding)】 由於 URL 不能直接接受 UTF8 文字,所以要先轉成百分號編碼後,如此網址才能順利送給 HTTP Server 從 Indy 加入 Delphi 後,就可以用 IdURI.TIdURI 單元進行編碼 ...