Tag Info

Hot answers tagged

16

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 ...


13

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 ...


13

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 ...


11

It depends. With SQL functions (LANGUAGE sql), the answer is generally yes. Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from within and pass parameters. With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


10

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 ...


10

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; ...


10

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 ...


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 ...


8

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: ...


7

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

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.


5

Here is an implementation I arrived upon when desiring to gain visibility into whether an insert or update occurred. The definition of upsert_data is to consolidate the values into a single resource, rather than having to specify the price and item_id twice: Once for the update, again for the insert. WITH upsert_data AS ( SELECT ...


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

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'; ...


5

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 ...


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

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 ...


5

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 -- ...


5

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; ...


5

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 ...


5

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function. Demo CREATE OR REPLACE FUNCTION trg_demo() RETURNS TRIGGER AS $func$ DECLARE test ...


4

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 ...


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

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself. Consider this simplified form: CREATE FUNCTION store_distance(_lat double precision ...



Only top voted, non community-wiki answers of a minimum length are eligible