Hot answers tagged plpgsql
14
It can be done, using the following template:
CREATE TABLE tablename ( ... );
/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;
/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
...
9
PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:
Use views where possible
Where a view is not suitable, use an SQL function
Where an ...
9
You should be able to use auto-explain. Turn it on and
SET auto_explain.log_min_duration = 0;
and you should get the plans in your log for all statements run in that session.
You might also want to set
SET auto_explain.log_analyze = true;
but you'll essentially run everything double - once for 'real' and once to EXPLAIN ANALYZE on. During a ...
9
Index names in PostgreSQL
Index names are unique across a single database schema.
Index names cannot be the same as any other table, view, sequence, user-defined composite type or index in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)
If you do not care about the name of the index, you can ...
7
You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted.
CREATE TRIGGER moveDeleted
BEFORE DELETE ON restrictions
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();
CREATE FUNCTION ...
6
I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements.
Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. Your session could look like this:
...
6
Functions with LANGUAGE SQL are basically just batch files with plain SQL commands in a function wrapper accepting parameters. For anything more, as Jack wrote, the most mature language is PL/pgSQL (LANGUAGE plpgsql). It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for ...
6
I could not find a direct way to output the the "CONTEXT" line with a user-defined exception. This option is just not implemented (yet) in PostgreSQL 9.1. Read the manual here.
However, I found a ...
Workaround
... that should perform flawlessly. You can make plpgsql behave as desired by calling another function that raises the error for you. This works ...
6
Answer
The error occurs here:
CASE tmp_code
WHEN COALESCE(tmp_code,0)=0 THEN
Would have to be
CASE WHEN COALESCE(tmp_code,0)=0 THEN
You are mixing the two different syntax variants of PL/pgSQL CASE ("simple case" vs. "searched case") in an incompatible way.
Another error
There is another error:
update netcen.test set
test=myobj.test,
...
6
You don't need a the function at all, this can be done with a single SQL statement:
with recursive tree as (id, parent) (
select link as id,
parent
from linktable
where id = itemid
union all
select c.link as id,
c.parent
from linktable c
join tree p on p.id = c.parent
)
select dt.id, dt.value
from tree
...
5
In terms of DDL, Postgres does not have procedure objects, only functions. Postgres functions can return value(s) or void so they take on the roles of both functions and procedures in other RDBMSs. The word 'procedure' in the create trigger refers to a function.
In terms of the Postgres documentation, 'procedure' is also a synonym for the database object ...
5
In MSSQL, a stored procedure is a pre-compiled set of sql commands.
A stored procedure:
- can have many input and output paramters
- can be used to modify database tables/structures/data
- are not normally used inside insert/update/delete/select statements
User defined functions come in several flavors. Depending on the type of function written, ...
5
Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in ...
5
Exception blocks are meant for trapping errors, not checking conditions. In other words, if some condition can be handled at compile time, it should not be trapped as error but resolved by ordinary program logic.
In Trapping Errors section of PL/PgSQL documentation you can find such tip:
Tip: A block containing an EXCEPTION clause is significantly more
...
5
While klin is technically right in his answer about how to fix your current function, let me question your whole approach. What you try to achieve is called 'UPSERT' and with PostgreSQL 9.1 (which offers writable CTEs) you have a very simple way to achieve this. I omitted the function definition for the sake of clarity, but you can easily wrap it in a ...
5
In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default.
Version 9.0 also introduced CREATE OR REPLACE LANGUAGE:
CREATE OR REPLACE LANGUAGE will either create a new language, or
replace an existing definition. If the language already exists, its
parameters are updated according to the values specified or taken from
pg_pltemplate ...
To ...
4
That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.
Here is a demo how to get around this:
CREATE TYPE mytype AS (id int, txt text);
DO
$body$
DECLARE
_val mytype := (1, NULL)::mytype;
_name text := 'txt';
...
4
Generally speaking moving application logic into the database will mean it is faster - after all it will be running closer to the data.
I believe (but am not 100% sure) that SQL language functions are faster than those using any other languages because they do not require context switching. The downside is that no procedural logic is allowed.
PL/pgSQL is ...
4
This particular example can be simpler.
You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement:
CREATE OR REPLACE FUNCTION truncate_tables(_username text)
RETURNS void AS
$func$
BEGIN
EXECUTE (
SELECT 'TRUNCATE TABLE '
|| string_agg(quote_ident(t.tablename), ', ')
|| ' CASCADE;'
...
4
Try dispensing with the explicit cursor:
begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table foo(id serial);
insert into foo default values;
create or replace function truncate_tables(username in varchar) returns void as $$
declare r record;
begin
for r in (select tablename ...
4
plpgsql is a full-fledged procedural language, with variables, looping constructs, etc. A SQL function is simply a subquery. A SQL function, if it is declared STABLE or IMMUTABLE and not also declared STRICT, can often be inlined into the calling query, as if it were written out on each reference.
4
Use
GET DIAGNOSTICS integer_var = ROW_COUNT;
More in the manual in the chapter Obtaining the Result Status.
Your example could look like this:
CREATE OR REPLACE FUNCTION f_test()
RETURNS SETOF table_a AS
$func$
DECLARE
i int;
ct int := 0;
BEGIN
RETURN QUERY SELECT * FROM table_a; -- 14 records
GET DIAGNOSTICS i = ROW_COUNT; ct := ct + i;
...
4
Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (
id serial PRIMARY KEY,
customerid int,
daterecorded date,
value double precision
)', 't_' || t_name);
END
$func$ LANGUAGE plpgsql;
I am using format() with %I to ...
3
The DO command has no facility to actually return data (except with RAISE, or you could write to a (temp) table .. ).
You need to create a PL/pgSQL function that can define a return type with RETURNS and call it.
You could return the result with RETURN QUERY EXECUTE. But I suspect the whole operation can be simplified ...
Rewrite as single SQL query
You ...
3
You can do some very interesting stuff using user defined functions (UDF) in postgresql. For instance, there's dozens of possible languages you can use. The built in pl/sql and pl/pgsql are both capable and reliable and use a sandbox method to keep users from doing anything too terribly dangerous. UDFs written in C give you the ultimate in power and ...
3
The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:
RETURN NULL;
That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.
You need to do this in a BEFORE trigger or issue an explicit UPDATE ...
3
The preferred method according a similar StackOverflow question is currently the following:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
...
3
The terms "stored procedure" and "stored function" are used interchangeably in PostgreSQL and are generally taken to mean the same thing. Other databases may differentiate between a procedure and function (much like how VB differentiates between subroutines and functions).
As long as a function in PostgreSQL returns something that resembles a table, you can ...
3
In PostgreSQL, every table name serves as type name automatically. So you can just declare a variable of that type in PL/pgSQL.
CREATE FUNCTION foo()
RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
q1 foo;
q2 bar;
BEGIN
FOR q1 IN
SELECT * from foo
LOOP
FOR q2 IN
SELECT * from bar
LOOP
-- do something with q1 and q2
-- ...
3
There are a few ways of doing this in PostgreSQL depending on the versions you have to support. The simplest is:
create or replace function plpgSqlProc () returns setof integer as $$
BEGIN
RETURN QUERY select * from foo;
END;
$$ LANGUAGE PLPGSQL;
This being said, I find where you don't need helper procedural code, plain SQL functions are usually better. ...
Only top voted, non community-wiki answers of a minimum length are eligible
