SQL Postgresql - Passar parâmetros fora de ordem com estrutura de tabela
Pessoal, boa tarde!
Tenho uma função em pspgsql no BD Postgresql que recebe como parâmetro uma tabela. Dentro da função a estrutura é reconhecida (veja ptabela.nome existe) porém não consigo passar os parâmetros para esta função.
Como posso fazer isso, inclusive indicando quais colunas quero passar por parâmetro? A tabela tem 6 colunas e só quero passar valor para uma delas..
EXEMPLO da dúvida:
select * from sp_tb_usuario_insert() ---- assim chama e não apresenta erro, mas também não funciona como deveria
Gostaria de poder chamar algo assim:
select * from sp_tb_usuario_insert(nome = 'Conteudo nome').
Onde: a tabela tb_usuario tem as seguintes colunas
id int
status bigint
nome character varying
Função que escrevi:
CREATE OR REPLACE FUNCTION dbo.sp_tb_usuario_insert(ptabela dbo.tb_usuario default null)
RETURNS TABLE (rretorno int, rmensagem character varying) AS
$BODY$
DECLARE
BEGIN
--ptabela.id = 29;
-- ptabela.nome = 'Alexandre';
RAISE NOTICE 'Mensagem % %', ptabela.nome, ptabela.id;
-- RETURN QUERY select cast(0 as int), ptabela.nome from dbo.tb_usuario;
RETURN QUERY select cast(0 as int), cast('xxxx' as character varying);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Obrigado!
Tenho uma função em pspgsql no BD Postgresql que recebe como parâmetro uma tabela. Dentro da função a estrutura é reconhecida (veja ptabela.nome existe) porém não consigo passar os parâmetros para esta função.
Como posso fazer isso, inclusive indicando quais colunas quero passar por parâmetro? A tabela tem 6 colunas e só quero passar valor para uma delas..
EXEMPLO da dúvida:
select * from sp_tb_usuario_insert() ---- assim chama e não apresenta erro, mas também não funciona como deveria
Gostaria de poder chamar algo assim:
select * from sp_tb_usuario_insert(nome = 'Conteudo nome').
Onde: a tabela tb_usuario tem as seguintes colunas
id int
status bigint
nome character varying
Função que escrevi:
CREATE OR REPLACE FUNCTION dbo.sp_tb_usuario_insert(ptabela dbo.tb_usuario default null)
RETURNS TABLE (rretorno int, rmensagem character varying) AS
$BODY$
DECLARE
BEGIN
--ptabela.id = 29;
-- ptabela.nome = 'Alexandre';
RAISE NOTICE 'Mensagem % %', ptabela.nome, ptabela.id;
-- RETURN QUERY select cast(0 as int), ptabela.nome from dbo.tb_usuario;
RETURN QUERY select cast(0 as int), cast('xxxx' as character varying);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Obrigado!
Alexandre Assi
Curtidas 0
Melhor post
Jair N.
25/09/2014
Bom Dia essa tabela "tb_usuario" você quer retonar, um tipo RECORD, cara não entendi nada do que você fez, você tá com pensamento em SQLServer tentando criar em PostgreSQL da mesma maneira?, tem que seguir os comandos de cada banco, assim não funciona mesmo.
GOSTEI 1
Mais Respostas
Ronaldo Lanhellas
24/09/2014
Vamos lá, pelo que entendi você quer atualizar apenas 1 campo daquela tabela usuario, passando o nome da coluna e o valor, mas você vai precisar passar também o id para saber qual registro vai atualizar.
CREATE FUNCTION atualiza_tabela ( nome_coluna varchar, valor_coluna pg_catalog."varchar", p_id integer ) RETURNS void AS $body$ BEGIN EXECUTE 'UPDATE esquema.tabela_usuario SET ' || nome_coluna || ' = ' || valor_coluna || ' WHERE id = '||p_id; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
GOSTEI 0
Alexandre Assi
24/09/2014
Bom dia Jair, obrigado pelo retorno.
Eu quero receber um "record", da tabela tb_usuario, e utilizar os alguns campos. Desconsidere o que a função executa neste momento, somente vamos nos atentar ao que se refere à passagem de parâmetros.
Objetivos:
1) Não ter que incluir uma coluna nova como parâmetro todas as vezes que criar uma coluna nova na tabela, evitando assim prototipar a função todas as vezes.
2) Passar como parâmetro algumas colunas e não todas, todas as vezes.
Como você menciona, sei que no SQL Server funciona, e estou tentando encontrar uma similaridade no Postgres.
--- Função simplificada
CREATE OR REPLACE FUNCTION sp_tb_usuario_insert(ptabela tb_usuario default null)
RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'Mensagem % %', ptabela.nome, ptabela.id; ****** duas colunas que existem na estrutura de registro da tabela (record) tb_usuario
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--- Chamada que testei e funciona
select sp_tb_usuario_insert(t.*) from tb_usuario t
--- Chamada que gostaria e que não consegui fazer funcionar
select * from sp_tb_usuario_insert(nome := 'Marcio Araujo')
Pensei também em algo do tipo, porém também não funciona:
DECLARE trecord tb_usuario%ROWTYPE;
trecord.nome = 'Marcio Araujo';
select * from sp_tb_usuario_insert(trecord);
Está difícil encontrar literatura que explique passagem de parâmetros por endereço, e também passagem de parâmetros fora de ordem.
Obrigado pela ajuda.
Eu quero receber um "record", da tabela tb_usuario, e utilizar os alguns campos. Desconsidere o que a função executa neste momento, somente vamos nos atentar ao que se refere à passagem de parâmetros.
Objetivos:
1) Não ter que incluir uma coluna nova como parâmetro todas as vezes que criar uma coluna nova na tabela, evitando assim prototipar a função todas as vezes.
2) Passar como parâmetro algumas colunas e não todas, todas as vezes.
Como você menciona, sei que no SQL Server funciona, e estou tentando encontrar uma similaridade no Postgres.
--- Função simplificada
CREATE OR REPLACE FUNCTION sp_tb_usuario_insert(ptabela tb_usuario default null)
RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'Mensagem % %', ptabela.nome, ptabela.id; ****** duas colunas que existem na estrutura de registro da tabela (record) tb_usuario
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--- Chamada que testei e funciona
select sp_tb_usuario_insert(t.*) from tb_usuario t
--- Chamada que gostaria e que não consegui fazer funcionar
select * from sp_tb_usuario_insert(nome := 'Marcio Araujo')
Pensei também em algo do tipo, porém também não funciona:
DECLARE trecord tb_usuario%ROWTYPE;
trecord.nome = 'Marcio Araujo';
select * from sp_tb_usuario_insert(trecord);
Está difícil encontrar literatura que explique passagem de parâmetros por endereço, e também passagem de parâmetros fora de ordem.
Obrigado pela ajuda.
GOSTEI 0
Alexandre Assi
24/09/2014
Ronaldo, bom dia
Obrigado pela resposta!
A intenção final é sim atualizar e manipular a tabela com um pouco mais de dinamismo, sem me preocupar em atualizar e criar parâmetros a cada modificação da tabela (quando houver).
A solução enviada por você atende em um aspecto e resolve o objetivo final. Entretanto estou procurando a alternativa, para este e outros fins, da passagem de parâmetros sem a necessidade de um protótipo da função campo a campo. Você conhece ou já viu literatura com isso?
Obrigado!
Obrigado pela resposta!
A intenção final é sim atualizar e manipular a tabela com um pouco mais de dinamismo, sem me preocupar em atualizar e criar parâmetros a cada modificação da tabela (quando houver).
A solução enviada por você atende em um aspecto e resolve o objetivo final. Entretanto estou procurando a alternativa, para este e outros fins, da passagem de parâmetros sem a necessidade de um protótipo da função campo a campo. Você conhece ou já viu literatura com isso?
Obrigado!
GOSTEI 0
Ronaldo Lanhellas
24/09/2014
Bom, se você quer parametrizar até o nome da tabela basta colocar ela como parâmetro também. Minha sugestão é que se você precisa atualizar mais de uma coluna da mesma tabela, poderia deixar a função do jeito que está (simples) e chamar várias vezes para a quantidade de colunas que deseja atualizar, vou dar um exemplo:
Segue a função com o parâmetro nome_tabela adicionado:
--Atualiza nome do usuario
select atualiza_tabela('nome','ronaldo',1,'core.usuario');
--Atualiza descricao de um item
select atualiza_tabela('item','descricao',1345,'estoque.item');
Segue a função com o parâmetro nome_tabela adicionado:
CREATE FUNCTION atualiza_tabela ( nome_coluna varchar, valor_coluna pg_catalog."varchar", p_id integer, nome_tabela varchar ) RETURNS void AS $body$ BEGIN EXECUTE 'UPDATE '||nome_tabela||' SET ' || nome_coluna || ' = ' || valor_coluna || ' WHERE id = '||p_id; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
GOSTEI 1
Alberto
24/09/2014
Aqui um exemplo de como receber parametros em uma function no postgre:
CREATE FUNCTION public.delete_algo()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE NOT LEAKPROOF
AS $BODY$
Declare arg Text;
BEGIN
arg := TG_ARGV[0];
Delete from tabela where campo = arg;
RETURN OLD;
END;
$BODY$;
GOSTEI 0
Paulo Hendrix
24/09/2014
olha vc tem que ser mais, libera. pega apenas o cod; onde esta a função para insert e onde quer mudar. e usa o </> para melhorar nosso entendimento... se poder ou não tiver solucionado seu problema. aproveita e posta negativo ou positivo do codigo
preciso do resultado do código que erro deu ou se foi. abraç...
select * from sp_tb_usuario_insert nome ~~* 'Marcio Araujo';
preciso do resultado do código que erro deu ou se foi. abraç...
GOSTEI 0