Passing parameters to procedure in postgresSQL

Question

Passing parameters to procedure in postgresSQL

Navigation

#1 by (2 votes)

2

I will create a trigger in postgreSQL to copy information from one table to another, the tables will always have the same structure, so I want to pass the table names, make a WHILE and copy the files from one to another, but I can not remember how to pass the parameters in text (Name of the tables) for this trigger, because the ones I did so far, did not need to pass parameters, just did something in predefined tables. >

CREATE OR REPLACE FUNCTION copiartabela ()
RETURNS INTEGER
AS $$

-- Função (Aqui utilizarei o nome das tabelas)

$$ LANGUAGE plpgsql;

sql procedure postgresql

asked by anonymous 17.12.2014 / 13:15

1 answer

Define the size of the field return Upload only with jQuery.ajax and PHP

score 2 · Accepted Answer

A very simple function that copies data from one table to another in PostgreSQL can be implemented as follows:

CREATE OR REPLACE FUNCTION copiartabela(IN source VARCHAR, IN target VARCHAR) RETURNS VOID AS $$
BEGIN
  EXECUTE 'insert into ' || target || ' select * from ' || source;
END;
$$ LANGUAGE PLPGSQL;

The function is declared with the parameters source and target , respectively, the name of the source table and the name of the target table.

The term IN before the parameters defines that they are both input parameters, if it were OUT would be output. The type of the parameters is text, represented by VARCHAR . The% wrapper defines that the function does not return a value.

Within the function, the command RETURNS VOID allows executing any dynamic query. The values passed to this command are a concatenation of values using the EXECUTE operator that assembles a dynamic insert.

The query generated by concatenation is a || command, which inserts data into the target table from a INSERT into the source table.

See a functional example of the function in ** SQLFiddle . **

Notes

Be careful not to confuse the terms function (function), procedure and trigger (trigger). Function is usually a routine that returns a value; procedure is usually related to a routine that executes several commands and may or may not have output parameters; trigger is a routine executed when some action occurs in the database, for example, a record is inserted into a table.
In PostgreSQL there is no such clear distinction between functions and procedures, since a SELECT can represent either one or the other.