[SQL] Unable to get UPDATE ... FROM syntax correct
I have two tables: Table "public.audit" Column |Type | Modifiers +--+--- id | integer | (serial) record_id | integer | not null key| character varying| (...) Table "public.extension" Column | Type | Modifiers -++ id | integer| (serial) number | integer| (...) The column "key" was recently added to the "audit" table, and I wish to populate it with the value form the "number" column in the extension table. The join between the tables is audit.record_id = extension.id I tried: UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id = extension.number; But that returns saying "UPDATE 0" However, doing: SELECT audit.record_id, extension.number FROM audit, extension WHERE audit.record_id = extension.id; Works fine. Can someone tell me what I'm doing wrong ? Thanks, GTG -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] sintax error
Hi there, i have got the followed error in the pgsql function bellow. Log messages point to: ERROR: syntax error at end of input LINE 1: SELECT ^ QUERY: SELECT CONTEXT: SQL statement in PL/PgSQL function "cms_permission__revoke_permission" near line 67 ** Error ** ERROR: syntax error at end of input SQL state: 42601 Context: SQL statement in PL/PgSQL function "cms_permission__revoke_permission" near line 67 However, I couldn't find any sintax error in those SELECT statements does anyone have any idea what is wrong with it? cheers, iuri -- procedure revoke_permission create or replace function cms_permission__revoke_permission (integer,integer,varchar,integer,varchar) returns integer as ' declare p_item_idalias for $1; p_holder_id alias for $2; p_privilege alias for $3; p_revokee_id alias for $4; p_is_recursive alias for $5; -- default ''f'' -- v_items item_array_type; v_item_idcr_items.item_id%TYPE; v_idxinteger; v_count integer; -- v_perms perm_array_type; v_perm acs_privileges.privilege%TYPE; v_perm_idx integer; v_perm_count integer; c_perm_cur record; c_item_cur record; begin PERFORM update_permissions(p_item_id, p_is_recursive); -- Select the child permissions v_perm_count := 0; for c_perm_cur in select child_privilege from acs_privilege_hierarchy where privilege = p_privilege and child_privilege <> p_privilege LOOP v_perm := c_perm_cur.child_privilege; v_perm_count := v_perm_count + 1; -- v_perms(v_perm_count) := v_perm; update v_perms set value[v_perm_count] = v_perm; end LOOP; -- Select child items v_count := 0; for c_item_cur in select c1.item_id from cr_items c1, cr_items c2 where c2.item_id = p_item_id and c1.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey) and cms_permission__has_revoke_authority ( item_id, p_holder_id, p_privilege, p_revokee_id ) = ''t'' and acs_permission__permission_p ( item_id, p_revokee_id, p_privilege ) = ''t'' LOOP v_item_id := c_item_cur.item_id; v_count := v_count + 1; -- v_items(v_count) := v_item_id; update v_items set value[v_count] = v_item_id; exit when p_is_recursive = ''f''; end loop; if v_count < 1 then return; end if; -- Grant child permissions for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop PERFORM acs_permission__grant_permission ( -- v_items(v_idx), p_revokee_id, v_perms(v_perm_idx) v_items.value[v_idx], p_revokee_id, v_perms.value[v_perm_idx] ); end loop; end loop; -- Revoke the parent permission for v_idx in 1..v_count loop PERFORM acs_permission__revoke_permission ( -- v_items(v_idx), v_items.value[v_idx], p_revokee_id, p_privilege ); end loop; return 0; end;' language 'plpgsql'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to get UPDATE ... FROM syntax correct
On 19/02/2010 10:28, "zkn" wrote: > update audit set key = (select extension.number from extension where > audit.record_id = extension_id) No joy, I still get "UPDATE 0" :-( GTG -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to get UPDATE ... FROM syntax correct
update audit set key = (select extension.number from extension where audit.record_id = extension_id) On 19.02.2010, at 11:25, Gordon Ross wrote: > I have two tables: > > Table "public.audit" > Column |Type | Modifiers > +--+--- > id | integer | (serial) > record_id | integer | not null > key| character varying| > (...) > > > Table "public.extension" > Column | Type | Modifiers > -++ > id | integer| (serial) > number | integer| > (...) > > > The column "key" was recently added to the "audit" table, and I wish to > populate it with the value form the "number" column in the extension table. > The join between the tables is audit.record_id = extension.id > > I tried: > > UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id > = extension.number; > > But that returns saying "UPDATE 0" > > However, doing: > > SELECT audit.record_id, extension.number FROM audit, extension WHERE > audit.record_id = extension.id; > > Works fine. > > Can someone tell me what I'm doing wrong ? > > Thanks, > > GTG > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to get UPDATE ... FROM syntax correct
On 19/02/10 09:25, Gordon Ross wrote: UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id = extension.number; But that returns saying "UPDATE 0" Works here - are you sure you don't have any triggers interfering? BEGIN; CREATE TEMP TABLE audit ( idint, record_id int, key varchar, PRIMARY KEY (id) ); CREATE TEMP TABLE extension ( id int, number int, PRIMARY KEY (id) ); INSERT INTO audit VALUES (1,1,''), (2,2,''), (3,3,''); INSERT INTO extension VALUES (-1,1), (-2,2), (-3,3); UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id = extension.number; SELECT * FROM audit ORDER BY id; ROLLBACK; CREATE TABLE INSERT 0 3 INSERT 0 3 UPDATE 3 id | record_id | key +---+- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sintax error
On 19/02/10 07:52, iuri de araujo sampaio wrote: Hi there, i have got the followed error in the pgsql function bellow. Log messages point to: ERROR: syntax error at end of input LINE 1: SELECT ^ QUERY: SELECT CONTEXT: SQL statement in PL/PgSQL function "cms_permission__revoke_permission" near line 67 ** Error ** ERROR: syntax error at end of input SQL state: 42601 Context: SQL statement in PL/PgSQL function "cms_permission__revoke_permission" near line 67 However, I couldn't find any sintax error in those SELECT statements does anyone have any idea what is wrong with it? I thought it was the PERFORM statements, but it's not. The line number seems right, although the error message is misleading. > if v_count < 1 then > return; > end if; That bare return isn't legal. Try "return NULL" instead. The "error at end of input" means it was looking for an expression to return and couldn't find one. Oh - unrelated tips. 1. You can have multi-line comments with /* ... */ - very useful for debugging. 2. Any version of PostgreSQL from the last few years supports "dollar quoting". http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS That lets you do: create or replace function ... as $$ ... IF myvar = 'normal-quoting works here' THEN ... $$ language plpgsql; -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to get UPDATE ... FROM syntax correct
On 19/02/2010 10:36, "Richard Huxton" wrote: > Works here - are you sure you don't have any triggers interfering? Doh ! *blush* Yes, I did, to stop anyone tampering with the audit table ;-) Thanks for that. GTG -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to get UPDATE ... FROM syntax correct
On 19/02/10 10:58, Gordon Ross wrote: On 19/02/2010 10:36, "Richard Huxton" wrote: Works here - are you sure you don't have any triggers interfering? Doh ! *blush* Yes, I did, to stop anyone tampering with the audit table ;-) There you go - it worked :-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sintax error
Richard Huxton writes: > I thought it was the PERFORM statements, but it's not. The line number > seems right, although the error message is misleading. FWIW, things are better in HEAD: regression=# create function foo() returns int as $$ regression$# begin regression$# return; regression$# end$$ language plpgsql; ERROR: missing expression at or near ";" LINE 3: return; ^ regression=# regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
