I have a URLs table. They contain 

(id int primary key,
 url character varying unique, 
content character varying, 
last analyzed date). 

I want to create trigger or something(rule may be), so each time i make insert from my java program, it updates some single row if row with such URL exists. Else it should perform an Insert.

Please, can you provide a complete code in Postgresql. Thanks.

Postgresql, update if row with some unique value exists, else insert

I am creating a function in pgsql script language, and what I want to do in this point is iterate over the results of a query and for each row do something specific. My current try is the following, where `temprow` is declared as `temprow user_data.users%rowtype`. The code in question is the following:  
  
    FOR temprow IN
			SELECT * FROM user_data.users ORDER BY user_seasonpts DESC LIMIT 10
		LOOP
			SELECT user_id,user_seasonpts INTO player_idd,season_ptss FROM temprow;
			INSERT INTO user_data.leaderboards (season_num,player_id,season_pts) VALUES (old_seasonnum,player_idd,season_ptss);
		END LOOP;  
  
However I get the following error from this: `ERROR:  relation &quot;temprow&quot; does not exist`. If it&#39;s clear what I want to be done, could you point to me the right way to do it?

PostgreSQL - Iterate over results of query

Can I make SQL sub queries in Check constraint ?

I&#39;ve a `post` table with columns `id, owner`&lt;br /&gt;
I&#39;ve another table `action` with columns `user_id, post_id`&lt;br /&gt;
Table `user` with columns `id`

`post_id -&gt; post.id` and `user_id -&gt; user.id` also `post.owner -&gt; user.id`

Now I want to constraint `post(post_id).id != user_id` on table `action`

How is that possible ?

SQL Sub queries in check constraint

I am trying to loop through an integer array (`integer[]`) in a plpgsql function. Something like this:

    declare
        a integer[] = array[1,2,3];
        i bigint;
    begin
        for i in a
    loop 
        raise notice &quot;% &quot;,i;
    end loop;
    return true;
    end

In my actual use case the integer array `a` is passed as parameter to the function. I get this error:

&gt;     ERROR:  syntax error at or near &quot;$1&quot;
&gt;     LINE 1:   $1

How to loop through the array properly?

Iterating over integer[] in PL/pgSQL

I keep all my functions in a text file with `&#39;CREATE OR REPLACE FUNCTION somefunction&#39;`.  
So if I add or change some function I just feed the file to psql.

Now if I add or remove parameters to an existing function, it creates an overload with the same name and to delete the original I need type in all the parameter types in the exact order which is kind of tedious.

Is there some kind of wildcard I can use to `DROP` all functions with a given name so I can just add `DROP FUNCTION` lines to the top of my file?

DROP FUNCTION without knowing the number/type of parameters?

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:

* Does it matter which columns I select? Do they have any relation to what data I need to lock and then update?

        SELECT * FROM table WHERE x=y FOR UPDATE;

   vs

        SELECT 1 FROM table WHERE x=y FOR UPDATE;

* I can&#39;t do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?

Here is my function:

&lt;!-- language: lang-sql --&gt;

    CREATE OR REPLACE FUNCTION update_message(v_1 INTEGER, v_timestamp INTEGER, v_version INTEGER)
    RETURNS void AS $$
    DECLARE
    	v_timestamp_conv TIMESTAMP;
    	dummy INTEGER;
    BEGIN
    	SELECT timestamp &#39;epoch&#39; + v_timestamp * interval &#39;1 second&#39; INTO v_timestamp_conv;
    	SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
    	UPDATE my_table SET (timestamp) = (v_timestamp_conv) WHERE userid=v_1 AND version &lt; v_version;
    END;
    $$  LANGUAGE plpgsql;

Postgres SELECT ... FOR UPDATE in functions

As far as I can tell, my function properly resembles the samples I&#39;ve seen. Can someone clue me in as to how I get this to work?

	create or replace function get_user_by_username(
		username varchar(250),
		online boolean
		) returns setof record as $$
	declare result record;
	begin

		if online then 
			update users
			set last_activity = current_timestamp
			where user_name = username;
		end if;

		return query
		select
			user_id,
			user_name,
			last_activity,
			created,
			email,
			approved,
			last_lockout,
			last_login,
			last_password_changed,
			password_question,
			comment
		from
			users
		where
			user_name = username
		limit 1;

		return;
	end;
	$$ language plpgsql;

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning &quot;record&quot;

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&#39;ve managed to come up with a SQL statement that returns all the commands I need to execute: 

    SELECT &#39;TRUNCATE TABLE &#39; ||  tablename || &#39;;&#39; FROM pg_tables WHERE tableowner=&#39;MYUSER&#39;;

But I can&#39;t see a way to execute them programmatically once I have them.

Truncating all tables in a Postgres database

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?

I have a function:
    
    CREATE OR REPLACE FUNCTION test(x numeric)
    RETURNS character varying AS
    $BODY$
    DECLARE
    name   character varying(255);
    begin
     name =&#39;SELECT name FROM test_table where id=&#39;||x;
      
     if(name=&#39;test&#39;)then
      --do somthing
     else
      --do the else part
     end if;
    end;
    return -- return my process result here
    $BODY$
    LANGUAGE plpgsql VOLATILE

In the above function I need to store the result of this query:

    &#39;SELECT name FROM test_table where id=&#39;||x;

to the variable `name`.

How to process this?


Store query result in a variable using in PL/pgSQL

I want to write a function with `pl/pgsql`. 
I&#39;m using *PostgresEnterprise Manager v3* and using shell to make a function, but in the shell I must define return type. If I don&#39;t define the return type, I&#39;m not able to create a function.

How can create a function without return result, i.e a Function that creates a new table?

How to create function that returns nothing

I have this function in PostgreSQL, but I don&#39;t know how to return the result of the query:

    CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
      RETURNS SETOF RECORD AS
    $$
    BEGIN
        SELECT text, count(*), 100 / maxTokens * count(*)
        FROM (
            SELECT text
	    FROM token
	    WHERE chartype = &#39;ALPHABETIC&#39;
	    LIMIT maxTokens
        ) as tokens
        GROUP BY text
        ORDER BY count DESC
    END
    $$
    LANGUAGE plpgsql;

But I don&#39;t know how to return the result of the query inside the PostgreSQL function.

I found that the return type should be `SETOF RECORD`, right? But the return command is not right.

What is the right way to do this?

How to return result of a SELECT inside a function in PostgreSQL?

Being completely new to PL/pgSQL , what is the meaning of double dollar signs in [this function][1]:

    CREATE OR REPLACE FUNCTION check_phone_number(text)
    RETURNS boolean AS $$
    BEGIN
      IF NOT $1 ~  e&#39;^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$&#39; THEN
        RAISE EXCEPTION &#39;Wrong formated string &quot;%&quot;. Expected format is +999 999&#39;;
      END IF;
      RETURN true; 
    END;
    $$ LANGUAGE plpgsql STRICT IMMUTABLE;


  [1]: http://postgres.cz/wiki/PL/pgSQL_%28en%29

I&#39;m guessing that, in `RETURNS boolean AS $$`, `$$` is a placeholder.

The last line is a bit of a mystery: `$$ LANGUAGE plpgsql STRICT IMMUTABLE;`

By the way, what does the last line mean?

What are &#39;$$&#39; used for in PL/pgSQL

My code is:

    SELECT column_name
    FROM information.SCHEMA.columns
    WHERE table_name = &#39;aean&#39;

It returns column names of table `aean`.  
Now I have declared an array:

    DECLARE colnames text[]


How can I store select&#39;s output in colnames array.  
Is there any need to initialize colnames?

Store select query&#39;s output in one array in postgres

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 * from quote_ident($1) where quote_ident($1).id=1) THEN
    	 return 1;
    	END IF;
        return 0;
        END;
    $$ LANGUAGE plpgsql;
    
    select some_f(&#39;table_name&#39;);

And I got this:

    ERROR:  syntax error at or near &quot;.&quot;
    LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                                 ^
    
    ********** Error **********
    
    ERROR: syntax error at or near &quot;.&quot;

And here is the error I got when changed to this `select * from quote_ident($1) tab where tab.id=1`:

    ERROR:  column tab.id does not exist
    LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Probably, `quote_ident($1)` works, because without the `where quote_ident($1).id=1` part I get `1`, which means something is selected. Why may the first `quote_ident($1)` work and the second one not at the same time? And how could this be solved?

Table name as a PostgreSQL function parameter

How can I do such query in Postgres?

    IF (select count(*) from orders) &gt; 0
    THEN
      DELETE from orders
    ELSE 
      INSERT INTO orders values (1,2,3);

PostgreSQL IF statement

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I&#39;m trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the tool I have. This is what I have so far:

    for i in 1..25 LOOP
       insert into playtime.meta_random_sample
       select i, ID
       from   tbl
       order  by random() limit 15000
    end loop

Postgres FOR LOOP

I am writing a SP, using PL/pgSQL.  
I want to return a record, comprised of fields from several different tables. Could look something like this:

    CREATE OR REPLACE FUNCTION get_object_fields(name text)
      RETURNS RECORD AS $$
    BEGIN
      -- fetch fields f1, f2 and f3 from table t1
      -- fetch fields f4, f5 from table t2
      -- fetch fields f6, f7 and f8 from table t3
      -- return fields f1 ... f8 as a record
    END
    $$ language plpgsql; 

How may I return the fields from different tables as fields in a single record?

**[Edit]**

I have realized that the example I gave above was slightly too simplistic. Some of the fields I need to be retrieving, will be saved as separate rows in the database table being queried, but I want to return them in the &#39;flattened&#39; record structure.

The code below should help illustrate further:

    CREATE TABLE user (id int, school_id int, name varchar(32));
            
    CREATE TYPE my_type AS (
      user1_id   int,
      user1_name varchar(32),
      user2_id   int,
      user2_name varchar(32)
    );
            
    CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
      RETURNS my_type AS $$
    DECLARE
      result my_type;
      temp_result user;
    BEGIN
      -- for purpose of this question assume 2 rows returned
      SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
      -- Will the (pseudo)code below work?:
      result.user1_id := temp_result[0].id ;
      result.user1_name := temp_result[0].name ;
      result.user2_id := temp_result[1].id ;
      result.user2_name := temp_result[1].name ;
      return result ;
    END
    $$ language plpgsql

Return multiple fields as a record in PostgreSQL with PL/pgSQL

I am using Postgres for a django project and I am currently implementing a database backup/restore system that as simple as possible performs a pg_dump when the user clicks backup and then pg_restore when they click restore backup.

All seems fine and dandy until it actually tries to perform the pg_restore at which time it gives this error:

&gt; pg_restore: [archiver (db)] Error from TOC entry 3206; 0 0 COMMENT
&gt; EXTENSION plpgsql pg_restore: [archiver (db)] could not execute query:
&gt; ERROR:  must be owner of extension plpgsql Command was: COMMENT ON
&gt; EXTENSION plpgsql IS &#39;PL/pgSQL procedural language&#39;;

I&#39;ve looked into what plpgsql is etc and I understand that, and regarding the error I tried manually setting the &quot;owner of the extension&quot; to the user who executes the script and owns the database itself but that changed nothing, its really annoying since its erroring on an attempt to set a comment of all things

This is all created automatically by pg_dump so the comment line cant be removed and there are no flags to disable comments (that I&#39;m aware off), so I&#39;m truly stuck as to how to solve this issue.

PostgreSQL 9.1 pg_restore error regarding PLPGSQL

I want to do a large update on a table in PostgreSQL, but I don&#39;t need the transactional integrity to be maintained across the entire operation, because I know that the column I&#39;m changing is not going to be written to or read during the update.  I want to know if there is an easy way *in the psql console* to make these types of operations faster. 

For example, let&#39;s say I have a table called &quot;orders&quot; with 35 million rows, and I want to do this: 

    UPDATE orders SET status = null;

To avoid being diverted to an offtopic discussion, let&#39;s assume that all the values of status for the 35 million columns are currently set to the same (non-null) value, thus rendering an index useless.

The problem with this statement is that it takes a very long time to go into effect (solely because of the locking), and all changed rows are locked until the entire update is complete.    This update might take 5 hours, whereas something like 

    UPDATE orders SET status = null WHERE (order_id &gt; 0 and order_id &lt; 1000000);

might take 1 minute.   Over 35 million rows, doing the above and breaking it into chunks of 35 would only take 35 minutes and save me 4 hours and 25 minutes.

I could break it down even further with a script (using pseudocode here):

    for (i = 0 to 3500) {
      db_operation (&quot;UPDATE orders SET status = null
                     WHERE (order_id &gt;&quot; + (i*1000)&quot;
                 + &quot; AND order_id &lt;&quot; + ((i+1)*1000) &quot; +  &quot;)&quot;);
    }

This operation might complete in only a few minutes, rather than 35.  


So that comes down to what I&#39;m really asking.  I don&#39;t want to write a freaking script to break down operations every single time I want to do a big one-time update like this.   Is there a way to accomplish what I want entirely within SQL?


How do I do large non-blocking updates in PostgreSQL?

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 &#39;hello! &#39;::varchar || istr;
     $$ language sql;

Function f2() - ***language plpgsql***

     create  or replace function f2(istr  varchar)
     returns text as $$ 
     begin select &#39;hello! &#39;::varchar || istr; end;
     $$ language plpgsql;

 - Both *functions* can be called like `select f1(&#39;world&#39;)` or `select f2(&#39;world&#39;)`.

 - If I call `select f1(&#39;world&#39;)` the *output* will be:

        `hello! world`

 - And *output* for `select f2(&#39;world&#39;)`:

    &gt;  ERROR:  query has no destination for result data
    &gt;        HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
    &gt;        CONTEXT:  PL/pgSQL function f11(character varying) line 2 at SQL statement
    &gt;        ********** Error **********

 - I wish to know the difference and in which situations I should use `language sql` or `language plpgsql`.

Any useful link or answers regarding functions will much appreciated.

Difference between language sql and language plpgsql in PostgreSQL functions

I refer [this][1]  to enable  the debugger in the PostgreSQL server in order to debugging the plpgsql function by stepping through the codes using the pgadmin.


I have already set `shared_preload_libraries = &#39;$libdir/plugins/plugin_debugger.dll&#39;` in the `postgresql.conf`, run `pldbgapi.sql` , and restarted the server.  

These steps should have been run successfully and `plugin_debugger.dll` should be loaded successfully as can be verified by using the command `show shared_preload_libraries`, and I can see the debugging option in the context menu with a right click on a function in pgAdmin. 

 ![enter image description here][2]

When choosing &quot;Debugging&quot; --&gt; Debug, a window pops up to allow me to enter the values for the input parameters. But after that, when I press `OK`, it does not respond at all.

![enter image description here][3]

Any ideas or did I miss something in the setup of the debugger on the server??

I am using PostgreSQL 8.3 and pgAdmin 1.14

  [1]: http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQL-Debugger.html
  [2]: http://i.stack.imgur.com/Tf3sr.png
  [3]: http://i.stack.imgur.com/0vVpm.png

Debug PostgreSQL function using pgAdmin

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?

How do I get the primary key(s) of a table from Postgres via plpgsql?

In plpgsql, I want to get the array contents one by one from a two dimension array.

    DECLARE
      m varchar[];
      arr varchar[][] := array[[&#39;key1&#39;,&#39;val1&#39;],[&#39;key2&#39;,&#39;val2&#39;]];
    BEGIN
      for m in select arr
      LOOP
        raise NOTICE &#39;%&#39;,m;
      END LOOP;
    END;

But the above code returns:

    {{key1,val1},{key2,val2}}

in one line. I want to be able to loop over and call another function which takes parameters like:

    another_func(key1,val1)

Loop over array dimension in plpgsql

I&#39;m writing a function in PL/pgSQL, and I&#39;m looking for the simplest way to check if a row exists.  
Right now I&#39;m SELECTing an `integer` into a `boolean`, which doesn&#39;t really work. I&#39;m not experienced with PL/pgSQL enough yet to know the best way of doing this.

Here&#39;s part of my function:

    DECLARE person_exists boolean;
    BEGIN

    person_exists := FALSE;

    SELECT &quot;person_id&quot; INTO person_exists
      FROM &quot;people&quot; p
    WHERE p.person_id = my_person_id
    LIMIT 1;

    IF person_exists THEN
      -- Do something
    END IF;

    END; $$ LANGUAGE plpgsql;

*Update* - I&#39;m doing something like this for now:

    DECLARE person_exists integer;
    BEGIN

    person_exists := 0;

    SELECT count(&quot;person_id&quot;) INTO person_exists
      FROM &quot;people&quot; p
    WHERE p.person_id = my_person_id
    LIMIT 1;

    IF person_exists &lt; 1 THEN
      -- Do something
    END IF;

PL/pgSQL checking if a row exists

I can successfully create a function as follows: 

    CREATE FUNCTION Foo(MY_Value INT) RETURNS INT
    AS &#39;SELECT 2 + MY_Value&#39;
    LANGUAGE SQL

However, if I first want to check if the function exists and then drop it if I does, I must specify the following:

    DROP FUNCTION IF EXISTS Foo(My_Value INT);

Without specifying the input parameters, the following returns an error stating &quot;NOTICE:  function foo() does not exist, skipping&quot;

    DROP FUNCTION IF EXISTS Foo();

Similar to MySQL, is there a way to drop a FUNCTION in PostgreSQL without having to specify the parameters to the function? In other words, is there an equivalent for the following in MySQL statement (i.e., drop the stored procedure without specifying the input parameters)?

    DROP PROCEDURE IF EXISTS Foo;

PostgreSQL: How to DROP FUNCTION IF EXISTS without specifying parameters?

My requirement is write optional parameters to a function.Parameters are optional sometimes i will add or i will not pass parameters to function.Can anyone help me how to write function.

I am writing like 

    select * 
    from test 
    where field3 in (&#39;value1&#39;,&#39;value2&#39;) 
     and ($1 is null or field1 = $1) 
     and ($2 is null or field2 = $2) 
     and ($3 is null or field3 = $3);

i am passing parameters to Query,But my output is not expected.when i pass all three parameters my output is correct,otherwise it is not expected output.


How to write function for optional parameters in postgresql?

With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I&#39;ve begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic to be embedded in a function. 

Is there a way to execute PL/PGSQL code without creating an executing a function?

All Plpgsql Solutions on Gang of Coders

Total of 27 Plpgsql Solutions