Passing parameters to the postgresSQL

Question

I will create a Rigger in postgresql to copy the information from one table to another, the tables will always have the same structure, so I intend to pass the table names, make a WHILE and copy the files from one to the other, only I can’t remember how to pass the parameters in text (Table name) to this Rigger, because the ones I did until now, 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;

Answer 1

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;

Function is declared with parameters source and target, respectively, the name of the source table and the name of the destination 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 excerpt RETURNS VOID defines that the function does not return a value.

Within the function, the command EXECUTE allows executing any dynamic query. The values passed for this command are a concatetion of values using the operator || that assemble a dynamic insertion.

The query generated by concatenation is a command INSERT, inserting data into the target table from a SELECT in the source table.

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

Notes

• Be careful not to confuse the terms Function (function), Procedure (procedure) and Trigger (trigger). Function is usually a routine that returns a value; Procedure is usually related to a routine that executes multiple commands and may or may not have output parameters; Trigger is a routine run when some action occurs in the database, for example, a record is inserted in a table.

• In Postgresql there is not such a clear distribution between functions and procedures, as a function can represent both one and the other.