Blog Post by Murat Yaşar


Posted on Friday, October 08, 2010 11:45:32 PM and it has been read 1778 times since then.


PostgreSQL - PL/pgSQL Little Code Snippets

Let's say we want to fill a table with data which will come from another server's database's table. First we need to install dblink.sql. You can find it under ~\PostgreSQL\9.0\share\contrib folder. After you install this script you can write the following query to copy data between tables resides in two different servers.

INSERT INTO "Customers"
SELECT resultCustomers.* FROM dblink('user=postgres dbname=Northwind port=5432 password=mypassword',
'SELECT "CustomerID", "CompanyName", "ContactName", "ContactTitle", "Address",
       "City", "Region", "PostalCode", "Country", "Phone", "Fax" FROM "Customers"') AS resultCustomers(
       CustomerID varchar(5),
       CompanyName varchar(40),
       ContactName varchar(30),
       ContactTitle varchar(30),
       Address varchar(60),
       City varchar(15),
       Region varchar(15),
       PostalCode varchar(10),
       Country varchar(15),
       Phone varchar(24),
       Fax varchar(24)
       );

Assume a table like this:

CREATE TABLE access_log (url varchar, client_ip inet);

To create a partial index that suits our needs, use a command such as this:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet ’192.168.100.0’ AND client_ip < inet ’192.168.100.255’);

"Above code has been taken from PostgreSQL Documentation"


I needed a function to take difference between two dates in seconds. I wrote a quick one and so far it works. You can try it out.

CREATE OR REPLACE FUNCTION "FP"."GetTimeDifferenceBetweenTwoDatesInSecond"(prm_end_date timestamp without time zone, prm_start_date timestamp without time zone)
  RETURNS bigint AS
$BODY$

DECLARE
	result bigint;

BEGIN

	SELECT INTO result (extract(epoch from (prm_end_date::timestamp - prm_start_date::timestamp)));

	RETURN result;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Let us say we need a function which will change column names after creating a temp table in postgresql.

CREATE OR REPLACE FUNCTION "COMPANY"."ChangingColumnNames"(schema_name text, table_name text)
  RETURNS void AS
$BODY$
DECLARE	
	r record;
	tablename text;		
	newcolumnname text;		
BEGIN

		SELECT INTO tablename '"' || schema_name || '"."' || table_name || '"';

		FOR r IN SELECT	pg_attribute.attname AS columnname
		FROM pg_catalog.pg_class
		JOIN pg_catalog.pg_namespace ON pg_class.relnamespace = pg_namespace.oid
		LEFT JOIN pg_catalog.pg_attribute ON pg_attribute.attrelid = pg_class.oid
		LEFT JOIN pg_catalog.pg_type ON pg_attribute.atttypid = pg_type.oid
		WHERE 
			attnum>0 AND 
			nspname=$1 AND 
			relname=$2 AND
			typname is not null
		ORDER BY 
			attnum
		LOOP

			newcolumnname := '';

			SELECT INTO newcolumnname
				CASE 
					WHEN r.columnname = 'Column1Old' THEN 'Column1New'
					WHEN r.columnname = 'Column2Old' THEN 'Column2New'					
					ELSE ''					
				END;
							

			IF r.columnname <> '' AND newcolumnname <> '' THEN
		
				EXECUTE 'ALTER TABLE ' || tablename || ' RENAME COLUMN "' || r.columnname || '" TO "' || newcolumnname || '"';
				
			END IF;		

		END LOOP;		

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Assume we need to find out days and their names between two dates then below script might be useful.

select
'2000-01-02'::date + s.a - 1 AS Saturday,
'2000-01-02'::date + s.a AS Sunday,
'2000-01-02'::date + s.a + 1 AS Monday,
'2000-01-02'::date + s.a + 2 AS Tuesday,
'2000-01-02'::date + s.a + 3 AS Wednesday,
'2000-01-02'::date + s.a + 4 AS Thursday,
'2000-01-02'::date + s.a + 5 AS Friday
from generate_series(0, '2020-12-31'::date - '2000-01-02'::date, 7) s(a)

In order to find column names in all tables.

SELECT
    table_name, column_name, *
FROM information_schema.columns
WHERE
    table_schema='SchemaName'
    --and table_name=<tablo_adi>
    --and column_name !~'[a-z]' and column_name <> 'ID'
    and column_name = 'OrderID'
    and is_updatable = 'YES'    -- YES : Tablo   NO : View
ORDER BY column_name 

Listing all date and date relevant information between two dates.

CREATE TYPE "Date" AS
   ("Date" date,
    "Year" integer,
    "Month" integer,
    "Day" integer,
    "Week" integer,
    "DayOfWeek" integer);

---

CREATE OR REPLACE FUNCTION "Date"(StartDate date, EndDate date)
RETURNS SETOF "Date" AS

$BODY$

DECLARE

 d date;
 Date "Date";

BEGIN

 d := StartDate;

 while (d <= EndDate) loop

  Date."Date" = d;
  Date."Year" = extract(year from d)::int;
  Date."Month" = extract(month from d)::int;
  Date."Day" = extract(day from d)::int;
  Date."Week" = extract(week from d)::int;
  Date."DayOfWeek" = case extract(dow from d) when 0 then 7 else extract(dow from d) end::int;

  return next Date;

  d := d + '1 Day'::interval;  
  
 end loop;

 return;

END

$BODY$
  LANGUAGE plpgsql VOLATILE

Have a great day.


(In order to use this feature, you have to register.)

Tag Related Blog Entries

Compare Tables Row By Row in PostgreSQL

Sunday, June 21, 2015 2   4940  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   3527  

Show All Functions and Views In PostgreSQL By Using Select Statement And Querying pgCatalog

Saturday, February 18, 2012 0   1088  

Npgsql Data Types And Equivalents

Monday, March 21, 2011 2   3182  

DataBinding GridView In Asp.NET Application By Using PostgreSQL as DataSource

Sunday, October 10, 2010 0   4170  

PostgreSQL 8.4.4 Data Types

Monday, September 27, 2010 0   1014  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part III

Saturday, January 23, 2010 0   1515