Blog Post by Murat Yaşar

1. Blog
2. PostgreSQL - PL/pgSQL Little Code Snippets
3. Technical

---

Posted on Friday, October 08, 2010 11:45:32 PM and it has been read 5380 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.

---

---