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

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?

Answers


Postgres 9

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

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


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;

Need Your Help

Rails Admin - List, Scope on association

ruby-on-rails scope rails-admin

I have categories and articles. They have a HABTM relation. I would like the possibility of listing each category and the articles that have relation to that article in the list view of articles.

Can't send both get and post http requests via html-forms on perl

forms perl post get http-request

I have switch-structure on my site. I work with get requests and I load different scripts, depending on what was in the query. There are switch-instruction in script.