[pgbr-geral] duvida com as aspas dentro de aspas ao criar função

Fri, 17 Jan 2014 05:56:03 -0800 

Ola a todos,
Estou a tentar criar uma função que crie sequencias em todas as tabelas num determinado esquema e não consigo avançar por causa das aspas dentro de aspas, alguém pode dar uma ajuda. 


Aqui está o código, e o problema com as aspas esta no primeiro e no 
terceiro EXECUTE:


CREATE OR REPLACE FUNCTION wosis.fun_create_sequence(sch text)
  RETURNS text AS
$BODY$
DECLARE
    row record;
BEGIN
    EXECUTE '  CREATE TEMP TABLE tmp AS

               SELECT table_name, table_name||_id' AS id_name, 0 AS 
next_value

               FROM information_schema.tables
               WHERE table_schema = sch
               AND  table_type = 'BASE TABLE'
               ORDER BY table_name;';
    FOR row IN
     SELECT table_name, id_name FROM tmp ORDER BY table_name
    LOOP

        EXECUTE 'UPDATE tmp SET next_value = (SELECT max(' || 
quote_ident(row.id_name) || ')+1 FROM ' || quote_ident(sch) || '.' || 
quote_ident(row.table_name) || ') WHERE table_name = ' || 
quote_ident(row.table_name) || ';'

    END LOOP;
    FOR row IN
     SELECT table_name, id_name, next_value FROM tmp ORDER BY table_name
    LOOP

        EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.table_name) || '_' 
|| quote_ident(row.id_name) || '_seq START ' || 
quote_ident(row.next_value) || ';'
        EXECUTE 'ALTER TABLE ' || quote_ident(sch) || '.' || 
quote_ident(row.table_name) || ' ALTER COLUMN ' || 
quote_ident(row.id_name) || ' SET DEFAULT nextval( ' || 
quote_ident(row.table_name) || '_' || quote_ident(row.id_name) || 
'_seq);'

    END LOOP;
RETURN 'Sequences were created';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION wosis.fun_create_sequence(text) OWNER TO postgres;

COMMENT ON FUNCTION wosis.fun_create_sequence(text) IS 'Create sequences 
in all tables from a given schema';


Obrigado,

--
Eloi Ribeiro
GIS Analyst / Programmer
51.9871, 5.6661
flavors.me/eloiribeiro
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral






















					Responder a