CODEWITHSUNDEEP
postgresqlnpgsql
El_L
El_L

Reputation: 355

Declaring Variables In Postgresql Queries ,NpgsqlCommand

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?

Upvotes: 0

Views: 300

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16672

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$$;

Upvotes: 1

Related Questions