How can I execute pl/pgsql code without creating a function?
PostgresqlPlpgsqlPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
Postgres 9
DO $$
-- declare
BEGIN
/* pl/pgsql here */
END $$;
Solution 2 - Postgresql
No, not yet. Version 9.0 (still alpha) will have this option (do), you a have to wait until it's released.
Solution 3 - Postgresql
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;