Hot answers tagged

52

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type 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 ...


25

Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. Currently (including pg 9.5) all statements in an SQL function are planned at once, which is subtly different from executing one statement after the other and may affect ...


24

UPDATE (2015-08-20): There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories. Here is an example, assuming item_id is your Primary ...


22

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


20

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


18

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


18

PostgreSQL now has UPSERT. 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 ...


16

As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine): The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. So this should work: SELECT ...


14

as superuser: create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from pg_largeobject where loid = l_oid order by pageno ) loop ...


14

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


14

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


11

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


11

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


10

This solution isn't exactly efficient in terms of runtime, but it's trivially easy compared to making your own headers for COPY BINARY. Further, it doesn't require any libraries or scripting languages outside of bash. First, convert the file into a hexdump, doubling the size of the file. xxd -p gets us pretty close, but it throws in some annoying newlines ...


9

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


9

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


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


8

Trigger functions can check the value of the TG_OP variable to determine the event which caused the trigger to fire. There are a number of other special variables as well listed in the documentation.


8

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


8

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


7

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though. Requires Postgres 9.0 or later (workaround for older versions possible). CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass) RETURNS void AS $func$ BEGIN FOR _tbl_in IN EXECUTE format('SELECT * FROM %s', pg_typeof(_tbl_in)) LOOP -- do ...


7

UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same. Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge ...


7

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


7

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. There is another error: update netcen.test set test=myobj.test, testname=myobj.testname ...


7

If you are open to a different approach, Have you considered adding a 'deleted' Boolean flag to the table, or a 'deleted_at' timestamp instead. Or better still, deny CRUD access to your database tables and handle the audit trail in your transactional API :)


7

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


6

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.


6

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


6

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


6

In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not a table type, there are no "table types" or "table variables" in Postgres (but there are typed tables). 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; ...



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