-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Ross Bagley wrote: > Newbie to pl/pgsql here. > > I'm trying to create a function that cleans up the foreign keys > referring to a particular row (if any exist), then removes the row (if > it exists), and returns the number of rows of br_role that were > deleted (0 or 1). Maybe I am missing something, but wouldn't ON DELETE CASCADE do what you need? > > Newbie stored procedure: > > CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ > BEGIN > DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; > RETURN DELETE FROM br_role WHERE role_pk = del_role_pk; > END; > $$ LANGUAGE plpgsql; > > Here's what hapens when I call it in psql using the "SELECT proc(...);" syntax: > > bedrock=> select delete_role(1892); > ERROR: column "delete" does not exist > CONTEXT: SQL statement "SELECT DELETE FROM br_role WHERE role_pk = $1 " > PL/pgSQL function "delete_role" line 4 at return > > Hm. That's not quite right. It should be returning the result of the > DELETE query, not the DELETE query itself. You don't return a query... you return the result of the query. Take a look at: http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT Section 37.6.3. Executing a Query with a Single-Row Result > > I did come across FOUND, which leads to this: > > CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ > BEGIN > DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role WHERE role_pk = del_role_pk; > IF FOUND THEN > RETURN 1; > ELSE > RETURN 0; > END IF; > END; > $$ LANGUAGE plpgsql; > > But this technique isn't usable in the next use case, where the number > of deleted rows may be more than one. Seems nasty to have immediate > values in the return statements, too. > > Seems like there should be some equivalent to FOUND that stores the > number of updated/deleted rows, but after reading over the docs a > couple of times, I haven't found it. > > So, how do I discover the number of rows deleted by a DELETE query? > > Thanks in advance, > Ross > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAq9zATb/zqfZUUQRAmiWAJ9SBttz97WqNPcOKCRX8PktneqaGQCfbS09 C6a02LkLzWgko9JuzjzGQaM= =6F9a -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
