How can I execute pl/pgsql code without creating a function?

Question

With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I've begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic to be embedded in a function.

Is there a way to execute PL/PGSQL code without creating an executing a function?

score 81 · Accepted Answer · edited Mar 17 '14 at 14:24

81

Postgres 9

DO $$ 
-- declare
BEGIN
  /* pl/pgsql here */
END $$;

edited Mar 17 '14 at 14:24

mpapec

50,217
8
67
127

answered Oct 25 '10 at 16:44

chotchki

4,258
5
34
55

4

For anyone who found this answer and tried it only to get "ERROR: syntax error at or near "SELECT"", you need a BEGIN and END. DO $$ BEGIN /* pl/pgsql here */ END $$ – Greg Najda Aug 26 '13 at 02:31
4

But how can that code return a query result? If I put a SELECT in the pl/pgsql part I get `ERROR: query has no destination for result data` – isapir Dec 29 '15 at 02:14
3

@Igal: It can't. You cannot return anything from a `DO` statement. You might raise notices or write to a temporary table or open a cursor as possible workarounds. – Erwin Brandstetter Apr 29 '16 at 02:23
little change work for me When i used `$$DECLARE` instead of `$$` ----------------------------------------------------------------------------------------------- `DO $$DECLARE srno integer; BEGIN select into srno count(reg_count) from table_name where condition; raise notice 'Desired Count %', srno; END $$;` – Er. Amit Joshi Nov 19 '18 at 04:31
I was happy to see this work with an EF Core migration – jenson-button-event Mar 01 '22 at 14:11

score 6 · Answer 2 · answered Apr 02 '10 at 22:31

6

No, not yet. Version 9.0 (still alpha) will have this option (do), you a have to wait until it's released.

answered Apr 02 '10 at 22:31

Frank Heikens

117,544
24
142
135

+1, but I think it was announced for 8.5 - http://developer.postgresql.org/pgdocs/postgres/release-8.5.html – Michael Krelin - hacker Apr 02 '10 at 22:33
1

I thought that 8.5 became 9.0 because of some major features that were implemented... – Apr 02 '10 at 22:37
Yep, 8.5 became 9.0. DO works very nice, just download the alpha and start testing: http://www.postgresql.org/download/snapshots – Frank Heikens Apr 03 '10 at 14:08
Frank, wish I knew where to download time to play with 9.0 ;-) – Michael Krelin - hacker Apr 04 '10 at 21:35
Is there documentation for now 9.6.0? – Moon soon Mar 21 '17 at 02:42
@TangMonk `DO` documentation for v9.6: https://www.postgresql.org/docs/9.6/static/sql-do.html – Davos Dec 06 '17 at 03:44

score 1 · Answer 3 · answered Dec 06 '17 at 04:06

I struggled to get this working because it's fairly strict about adding semi colons in exactly the right places. But once you get used to that it works well. Besides the inability to return records of course, however you can raise notices & exceptions and do the other workarounds like using temp tables as @ErwinBrandstetter pointed out in a comment above.

e.g.:

DO 
$$
BEGIN
  IF EXISTS(SELECT 'any rows?' 
              FROM {your_table} 
              WHERE {your_column} = 'blah')
  THEN
      RAISE NOTICE 'record exists';
  ELSE
      RAISE EXCEPTION 'record does not exist';
  END IF;

  DROP TABLE IF EXISTS foo;

  CREATE TEMP TABLE foo AS
  SELECT 'bar'::character varying(5) as baz;
END 
$$;

SELECT * FROM foo;

I think the ability to return rows, similar to SQL Server/Oracle stored procedures, is coming in the next version of PostgreSQL. Why that has taken this long is beyond me. — , Dec 07 '17 at 17:22

How can I execute pl/pgsql code without creating a function?

3 Answers3

Linked