Declaring Variables In Postgresql Queries ,NpgsqlCommand

Question

I convert from SQL Server to PostgreSQL

I used ADO.NET

My queries include defined variables inside the query

For example this

 SqlCommand sqlCommand=new SqlCommand(
                @"declare @tempId bigint 
                select top 1 @tempId = col1 from table 
                if @tempId is not null
                begin 
                select ...
                end 
                else 
                begin 
                select ...
                end ")

How do I do it with NpgsqlCommand?

SQL (the query language) doesn't support variables or an IF command and Postgres has no extension to the SQL language to support that. You will have to do that conditional logic in Ado.net or write a stored procedure using PL/pgSQL where variables and conditional execution is supported. — user330315
– user330315, Commented May 3, 2022 at 6:14
@a_horse_with_no_name Is it possible to do this without Saved SP? — El_L
– El_L, Commented May 3, 2022 at 8:05

Shay Rojansky · Accepted Answer · 2022-05-03 14:30:27Z

1

You can use a PostgreSQL anonymous code DO block to execute a fragment of procedural code, see the PG docs.

Example:

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_schema, table_name FROM information_schema.tables
             WHERE table_type = 'VIEW' AND table_schema = 'public'
    LOOP
        EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
    END LOOP;
END$$;

answered May 3, 2022 at 14:30

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Declaring Variables In Postgresql Queries ,NpgsqlCommand

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related