All Questions

44
votes
6answers
45604 views

Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select...
22
votes
3answers
13158 views

Refactor a PL/pgSQL function to return the output of various SELECT queries

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and ...
17
votes
3answers
4426 views

Is SELECT or INSERT in a function prone to race conditions?

I wrote a function to create posts for a simple blogging engine: CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[]) RETURNS INTEGER AS $$ DECLARE InsertedPostId INTEGER; TagName...
34
votes
4answers
5839 views

The forgotten assignment operator "=" and the commonplace ":="

The documentation for PL/pgSQL says, that declaration and assignment to variables is done with :=. But a simple, shorter and more modern (see footnote) = seems to work as expected: CREATE OR REP...
25
votes
2answers
16130 views

INSERT with dynamic table name in trigger function

I'm not sure how to achieve something like the following: CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$ DECLARE shadowname varchar := TG_TABLE_NAME || 'shadow'; ...
104
votes
8answers
87481 views

Truncating all tables in a Postgres database

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL? At the moment I've managed to come up with a SQL statement that returns all ...
50
votes
2answers
15042 views

What are '$$' used for in PL/pgSQL

Being completely new to PL/pgSQL , what is the meaning of double dollar signs in this function: CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\+\\...
9
votes
6answers
13664 views

How to set value of composite variable field using dynamic SQL

Given this type: -- Just for testing purposes: CREATE TYPE testType as (name text) I can get the value of a field dynamically with this function: CREATE OR REPLACE FUNCTION get_field(object anyele...
14
votes
2answers
10744 views

Difference between language sql and language plpgsql in PostgreSQL functions

Am very new in Database development so I have some doubts regarding my following example: Function f1() - language sql create or replace function f1(istr varchar) returns text as $$ select 'hell...
7
votes
2answers
3282 views

Unnest multiple arrays in parallel

My last question Passing an array to stored to postgres was a bit unclear. Now, to clarify my objective: I want to create an Postgres stored procedure which will accept two input parameters. One will...

Previous Next