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

Question

With SQL Server, I can execute ad hoc T-SQL code with full procedural logic through SQL Server Management Studio or any other client. I started working with PostgreSQL and faced with some difference that PGSQL requires some kind of logic to be built into the function.

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

Answer 1

Postgres 9

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

Answer 2

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

Answer 3

I struggled to get this to work, because it is pretty strict in adding half columns to the right places. But once you get used to it, it works well. Besides the inability to return records, of course, you can create notifications and exceptions and perform other workarounds, for example, using temporary tables, as indicated in the comment by @ErwinBrandstetter in the comment above.

eg:.

 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;