lundi 29 décembre 2014

How to use the value from record like as varchar?


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 |




Aucun commentaire:

Enregistrer un commentaire