Sample Header Ad - 728x90

How to use the value from record like as varchar?

1 vote
0 answers
100 views
I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; _oldid integer; _currentid integer; BEGIN OPEN _roles FOR SELECT role FROM roles WHERE id IN (SELECT idrole FROM user_roles WHERE iduser=4); EXECUTE format('SELECT oldid FROM users WHERE id = 4') INTO _oldid; EXECUTE format('SELECT id FROM oldtable WHERE id = $1') USING _oldid; FOR _record IN _roles LOOP RAISE NOTICE 'The role is = %', _record; EXECUTE FORMAT ('UPDATE oldtable SET ''%s'' = 1 WHERE id = $1', _record) USING _oldid; END LOOP; END; $$ LANGUAGE plpgsql; So, this return a sintaxe erro because the correspondent value for _record contain parentheses and apostrophe (e.g.: *UPDATE oldtable SET '(rolename)' = 1 WHERE id = 2*). Of course, these type is record and not varchar... Well, how to use the value from record like as varchar? ********* For more information if needed TABLE USER id | user -------------- 1 | 'user1' -------------- 2 | 'user2' -------------- TABLE ROLES id | role ---------------- 1 | 'role' ---------------- 2 | 'role2' ---------------- 3 | 'role3' ---------------- 4 | 'rolex' ---------------- TABLE USER_ROLES iduser | idrole ---------------- 1 | 1 ---------------- 1 | 2 ---------------- 1 | 3 ---------------- 1 | 4 ---------------- 2 | 3 ---------------- 2 | 2 ---------------- TABLE WRONG_OLD_TABLE **:)** iduser | role1 | role2 | role3 | role_x | role_y ... ----------------------------------------------------- 1 |1 |1 |1 |1 |1 | ----------------------------------------------------- 2 |0 |1 |1 |0 |0 |
Asked by GustavoAdolfo (173 rep)
Dec 29, 2014, 06:08 PM