如何在简单的PostgreSQL脚本中使用变量？

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

如何在简单的PostgreSQL脚本中使用变量？

DECLARE @List AS VARCHAR(8)
SELECT @List = 'foobar'
SELECT *
FROM   dbo.PubLists
WHERE  Name = @List

create table test (id int);
insert into test values (1);
insert into test values (2);
insert into test values (3);
create function test_fn() returns int as $$
    declare val int := 2;
    begin
        return (SELECT id FROM test WHERE id = val);
$$ LANGUAGE plpgsql;
SELECT * FROM test_fn();
 test_fn 
---------
       2

\set Comm 150
select sal, sal+:Comm from emp

DO $$
DECLARE v_List TEXT;
BEGIN
  v_List := 'foobar' ;
  SELECT *
  FROM   dbo.PubLists
  WHERE  Name = v_List;
  -- ...
END $$;

DO $$
DECLARE lastid bigint;
BEGIN
  INSERT INTO test (name) VALUES ('Test Name') 
  RETURNING id INTO lastid;
  SELECT * FROM test WHERE id = lastid;
END $$;

\set list '''foobar'''
SELECT * FROM dbo.PubLists WHERE name = :list;

DO $$
DECLARE  
   a integer := 10;  
   b integer := 20;  
   c integer;  
BEGIN  
   c := a + b;
    RAISE NOTICE'Value of c: %', c;
END $$;

CREATE OR REPLACE FUNCTION MYFUNC()
RETURNS VOID AS $$
BEGIN
DECLARE
 myvar int;
$$ LANGUAGE SQL;

CREATE TEMP TABLE list AS VALUES ('foobar');
SELECT dbo.PubLists.*
FROM   dbo.PubLists,list
WHERE  Name = list.column1;

DO $$
BEGIN
    IF NOT EXISTS(
        SELECT TRUE FROM pg_attribute 
        WHERE attrelid = (
            SELECT c.oid
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE 
                n.nspname = CURRENT_SCHEMA() 
                AND c.relname = 'device_ip_lookups'
        AND attname = 'active_date'
        AND NOT attisdropped
        AND attnum > 0
        RAISE NOTICE 'ADDING COLUMN';        
        ALTER TABLE device_ip_lookups
            ADD COLUMN active_date TIMESTAMP;
        RAISE NOTICE 'SKIPPING, COLUMN ALREADY EXISTS';
    END IF;
END $$;

DO $$ 
DECLARE name_pk VARCHAR(200);
BEGIN
select constraint_name

如何在简单的PostgreSQL脚本中使用变量？

9 个回答