1

I am attempting to create a View in PhpPgAdmin (PostGreSQL db) which has the following SQL statement:

DELETE FROM myTable WHERE myTable.error IS NULL;

PhpPgAdmin gives me the following error:

ERROR: syntax error at or near "DELETE" at character 59 In statement: CREATE OR REPLACE VIEW "Schema1"."Delete empty errors" AS DELETE FROM myTable WHERE myTable.error IS NULL;

As far as I can tell this SQL statement is valid, and I have delete privileges for the table. Is the DELETE statement not allowed in Views? Any ideas what I am doing wrong?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Ted1391
  • 139
  • 3
  • 12

1 Answers1

4

Views are used to display the data from SELECT statements only (usually when the SELECT is complex). Views cannot contain DELETES, UPDATES, or INSERTS.

Perhaps you want a function?

EDIT: As OMG Ponies points out, you can have updateable views, but thats where you would issue a DELETE to an existing view and then use a RULE to rewrite the query as a DELETE.

And please, please don't wrap a function call to do a DELETE as a side effect in a view. Its unexpected and Jesus shoots a puppy every time this happens.

rfusca
  • 7,435
  • 2
  • 30
  • 34
  • Postgres does support updatable views via rules: http://www.postgresql.org/docs/8.2/static/sql-createview.html – OMG Ponies Jun 30 '10 at 17:31
  • And a function call can be wrapped inside a view. – Frank Heikens Jun 30 '10 at 17:35
  • OK, you can have updatable views using rules, and can wrap a function call inside a view, but I've never seen a plain DELETE inside a view – pcent Jun 30 '10 at 17:58
  • While, yes, you can have updateable views, it didn't really appear to be what was required. He wasn't trying to DELETE from a view, he was trying to create a view that is a delete....which is different. – rfusca Jun 30 '10 at 19:00
  • Actually you are correct, I need to use a function. I'm just trying to execute a one time task to clean up a large dataset. So I took your advice and created a Function, but there doesn't seem to be a way to execute the function from within PhpPgAdmin. What is recommended for this sort of thing? – Ted1391 Jun 30 '10 at 21:00
  • select call_your_function_here(); – rfusca Jul 01 '10 at 01:50