1

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      |
  • I managed to do what I want using array, but I like to learn to use properly record types. Thanks. – GustavoAdolfo Dec 29 '14 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.