You have to use an intermediate cast:

SELECT my_numeric_string::text::int FROM my_table;

The line
path := '''';
is very important because if you concat a null with a string you get an empty string, so be sure to initialise 'path' with an empty string.



CREATE OR REPLACE FUNCTION getParents(oid) RETURNS VARCHAR AS 'DECLARE
folder_oid ALIAS FOR $1;
path varchar(255);
itemrecord RECORD;
BEGIN
     path := '''';
     SELECT D.* INTO itemrecord FROM folder D WHERE oid = folder_oid;
     path := (path || itemrecord.name);
     IF itemrecord.parent_oid IS NOT NULL THEN
          path := (getParents(itemrecord.parent_oid) || '' -> '' || path);
          RETURN path;
     ELSE
          RETURN path;
     END IF;
END' LANGUAGE 'plpgsql';


SELECT getParents(22990716);

You need to activate the language plpgsql for the database you are working with. This can be done using the postgres command line tool createlang:

createlang --dbname=my_database --username=postgres plpgsql

CREATE OR REPLACE FUNCTION test_del() RETURNS trigger AS
'BEGIN
     DELETE FROM test WHERE parent_test_oid = OLD.OID;
     RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER test_del_trig BEFORE DELETE ON test FOR EACH ROW EXECUTE PROCEDURE test_del();

I normally do cascading delete stuff with Postgres Rules but I had trouble with rules when the cascading delete was to delete from the same table. Here is a way around it. There might be better wasy but this does do the job.

1. Create the table:

CREATE TABLE test (
name VARCHAR(20),
parent_test_oid OID);

2. Create a unique index on the oid of the table:

CREATE UNIQUE INDEX test_oid ON test (oid);

3. Add a constraint to the table:

ALTER TABLE test ADD CONSTRAINT fk_parent_test_oid FOREIGN KEY (parent_test_oid) REFERENCES test (oid) ON DELETE CASCADE;

4. It should now be working. Create a parent record and some child records, then delete the parent record. The child records should automatically be deleted too.

Good explanation at: http://supportweb.cs.bham.ac.uk/documentation/postgres/manual/sql-createview.html

CREATE VIEW kinds AS
SELECT *
FROM films
WHERE kind = 'Comedy';

SELECT * FROM kinds;

Good example at: http://jamesthornton.com/postgres/7.0/programmer/xfunc.htm

create function TP1 (int4, float8) returns int4
as 'update BANK set balance = BANK.balance - $2
where BANK.acctountno = $1
select(x = 1)'
language 'sql';

Good explanation at: http://www.php-editors.com/postgres_manual/p_functions-conditional.html

SELECT a,
CASE
WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other

If you have a parent table and a child table with foreign keys in it referring to the parent table, you can set up a rule like this to delete the child recrods of a particular parent record if the parent is deleted:

CREATE RULE parent_del AS ON DELETE TO parent DO DELETE FROM child WHERE child.parent_oid = OLD.oid;

Note that the entity OLD refers to the row being deleted.

Toggle timing in psql:

\timing

Or for more detail and a query breakdown:

EXPLAIN ANALYZE SELECT ...

SELECT DATE(CURRENT_DATE + INTERVAL '1 DAY');

Good overview at: http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

This is a way to import database table data from Postgres into Visio, via MySQL. The sequence goes like this:

     Postgres dump schema (pg_dump)
     Convert Postgres dump
     MySQL import
     Visio import via MySQL ODBC

A much better way would be to have the Visio takling to Postgres ODBC directly, but I didn't have Postgres ODBC set up and had heard that MS products have difficulty talking to Postgres ODBC so I went down this other route instead as I just needed a quick dump of the table details.

Note that this method allows you to import only tables into Visio, not the relationship between tables, or indexes, etc - just the table schemas.

Create a dump of the postgres schema:

     pg_dump -U postgres -s xf_dev > xf_dev.sql

That file will be a series of sql statements to create the tables in Postgres. Some of the datatypes and syntax are different in MyS
QL, so I wrote this php script to translate the statements:

     $skip_lines = array("--", "REVOKE", "GRANT", "connect", "SET ");

     if($_SERVER['argc'] != 3) {
          exit("usage: pg2mysql.php pg_input_file mysql_output_file\n");
     }

     $input_file = $_SERVER['argv'][1];
     $output_file = $_SERVER['argv'][2];

     if(!file_exists($input_file)) {
          exit("Can't open input file: $input_filen");
     }

     $input = file_get_contents($input_file);

     $input = str_replace(" oid", " int", $input);
     $input = str_replace(" boolean", " bool", $input);
     $input = str_replace(" timestamp without time zone", " timestamp", $input);
     $input = str_replace(" timestamp with time zone", " timestamp", $input);
     $input = str_replace(" bool DEFAULT true", " bool DEFAULT 1", $input);
     $input = str_replace(" bool DEFAULT false", " bool DEFAULT 0", $input);
     $input = str_replace('"', "", $input);


     $lines = explode("n", $input);
     $output = "";

     for($i=0; $i<count($lines); $i++) {

          //Skip lines which start with words in the $skip_lines array:
          for($s=0; $s<count($skip_lines); $s++) {
               if(substr($lines[$i], 0, strlen($skip_lines[$s])) == $skip_lines[$s]) continue 2;
          }

          // Skip blank lines:
          if(!strlen(trim($lines[$i]))) continue;

          // Replace varchars greater that 255 with 'text' type:
          if($pos = strpos($lines[$i], " character varying(")) {

               $num = substr($lines[$i], $pos+19);
               $num = substr($num, 0, strpos($num, ")"));
               if($num > 255) {
                    $lines[$i] = str_replace(" character varying($num)", " text", $lines[$i]);
               }
          }

          // Copy this line to output:
          $output .= $lines[$i]."\n";
     }

     if(!$fp = fopen($output_file, "w")) exit("Can't open output file for writing.");
     fwrite($fp, $output);
     fclose($fp);

     print("don
e\n");

I run the script from the command prompt:

     c:phpphp pg2mysql.php xf_dev.sql mysql_xf_dev.sql

Which generates the translated file "mysql_xf_dev.sql".

Copy the statements from that file and paste them in at the mysql command line.

Install the mysql ODBC driver (available from the mysql website).

In Visio, choose 'reverse engineer' from the 'database' menu. From there you can select to connect via the generic ODBC driver, and you should see the mysql connection somewhere. Click on that and select the tables you want to import. Click "finish" and your tables should be imported.

If you're logged in as root:

/etc/init.d/postgres restart

Or

su - postgres -c 'pg_ctl restart -D /usr/local/pgsql/data'

If you're logged in as 'postgres':

pg_ctl restart -D /usr/local/pgsql/data

(note that these assume that 'pg_ctl' is in your path - if not, replace it with '/usr/local/pgsql/bin/pg_ctl' or whatever the path is)

pg_dump -s -u -f schema-2004-01-14.sql db_name

-s = get schema only (no data)
-u = ask for user name
-f = file to dump to

Define the procedure:

CREATE PROCEDURE mydb.times_two
@a int out,
@b int
AS
BEGIN
select @a = @b*2
END

Call the procedure:

DECLARE @x INT
EXECUTE mydb..times_two @a = @x output, @b = 5

Returns:

@a
----
10

Then SELECT @x returns:

----
10

select round(date_part('epoch', my_date)) from my_table;

If you don't have to log in etc:

          mysqldump -xml my_database

     Otherwise it'll be something like:

          mysqldump -h my.host.com -u username -p -xmp my_database

     and you'll be prompted for a password.

     Remember you can use the > operator to capture the mysqldump output as a text file.

(these methods were worked out by studying the information at
      http://www.mysql.com/doc/en/mysqldump.html)


     METHOD 1 - Backup to a text file for later restore, using mysqldump.

     To create the backup locally:

          mysqldump --opt my_database > my_database.sql

     To create the backup remotely:

          mysqldump --opt -u username -p -h my.host.com my_database > my_database.sql

     Note that you can specify several databases with the --databases option:

          mysqldump --opt --databases my_database_1 my_database_2 > my_databases.sql

     The --opt option is not essential but combines several of the other mysqldump options to provide
     optimum     output performance. For large databases, --opt prevents the entire database being loaded
     into memory before output begins (so --opt really is essential for large databases, otherwise
     mysqldump could try
to load 10GB of data into memory).

     To restore the backup:

          1. If the database to be restored doesn't exist, create it (it will be empty):

               create database my_database

          2. Enter

               mysql my_database < my_database.sql

           for local restoration or

               mysql -u username -p -h my.host.com my_database < my_database.sql

           for remote restoration.

     This demonstrates how MySQL commands in a text file can be executed from the command line.


     METHOD 2 - Pipe mysqldump output directly into another server:

          mysqldump --opt my_database | mysql -u username -p -h my.host.com -C my_database

     As in method 1, the database will have to exist on the target server, even if it's just
     an empty database.

     The --opt option is not essential but combines several of the other mysqldump options to provide
     optimum     output performance. For large databases, --opt prevents the entire database being
loaded
     into memory before output begins (so --opt really is essential for large databases, otherwise
     mysqldump could try to load 10GB of data into memory).

     The -C option tells the server to use compression (to speed up transer?).

     Note that this method could also be used to make copies of a database on the same server:

          1. Create the empty target database if it doesn't already exist:

               create database my_database_copy

          2. Enter:

               mysqldump --opt my_database | mysql -C my_database_copy


     METHOD 3 - User the perl script mysqlhotcopy

          Could be the fastest method, but I haven't tried it yet.
          Obviously this isn't included with the Windows MySQL distribution.

          See http://www.mysql.com/doc/en/mysqlhotcopy.html

The tables user, host and db specify what hosts/users can connect to the server or
     to particular databases. This actually makes sense when you think about it, and if
     you examine these tables, they're sort of self-explanitory.

     In most cases, I'll probably want to add a single user:

          insert into user values ("10.0.0.120", "Rangi", PASSWORD("mypassword"), "Y", "Y",
          "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y");

     Note the PASSWORD() function, which turns the password into a hash of some kind.
     Replace "10.0.0.120" with whatever domain/ip you are connecting from, or use "%"
     to allow this user to be able to connect from anywhere.

     *** ONCE YOU HAVE CHANGED THE mysql TABLE YOU MUST ENTER: ***

          mysqladmin flush-privileges
     
     (that bit is easy to forget but essential - at
tempting to log in without having done
      this will not work)     

     To connect from a remote machine from the command line you can then enter:

          mysql -u rangi -p -h 10.0.0.15 [name_of_database]

     You will be prompted for a password. Replace "10.0.0.15" with the domain/ip of the
     server you are connecting to.

From the command monitor (where you can type in sql statements), type:

     set password = password("my_new_password")

SELECT name, YEAR(birthday) FROM people GROUP BY YEAR(birthday);

following query shows, for each pet, the birth date, the current date, and the age in years.

     SELECT name, birth, CURRENT_DATE,
     (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

     SELECT Employees.Name, Orders.Product
     FROM Employees
     LEFT JOIN Orders
     ON Employees.Employee_ID=Orders.Employee_ID
     WHERE Employees.Name = 'Rangi';

SQL GROUP BY clause:     

     SELECT page_script, COUNT(page_script) AS hits FROM page_hit GROUP BY page_script;

     will return:

     +-----------------------------+------+
     | page_script | hits |
     +-----------------------------+------+
     | /www/basket.php | 2 |
     | /www/comp.php | 1 |
     | /www/image.php | 2 |
     | /www/index.php | 11 |
     | /www/lightbox.php | 1 |
     | /www/popups/basket_calc.php | 3 |
     | /www/search.php | 5 |
     | /www/user/login.php | 6 |
     +-----------------------------+------+

     and

     SELECT ((max(ts) - min(ts))/60) AS minutes FROM page_hit GROUP BY sid;

     will return:

     +---------+     (this returns the number of minutes that each session took, and from this we can
     | minutes |      work out the average
session length)
     +---------+
     | 3.20 |
     | 36.67 |
     +---------+

     and

     SELECT COUNT(DISTINCT sid) AS users_online FROM page_hit WHERE UNIX_TIMESTAMP(ts) > (UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - (60 * 3));

     will return:

     +-----------+     (this returns the number of people that have loaded a page in the last 3 minutes)
     | count(ts) |     (this includes people that are just browsing - they don't have to be logged in)
     +-----------+
     | 3 |
     +-----------+

     A slight variation shows the number of people _logged_in_ (not just browsing):

     SELECT COUNT(DISTINCT sid) AS users_logged_in FROM page_hit WHERE UNIX_TIMESTAMP(ts) > (UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - (60 * 3)) AND FK_webuser_no IS NOT NULL;

     Another variation shows the number of users online that are not logged in:

     SELECT COUNT(DISTINCT sid) AS users_online_not_logged FROM page_hit WHERE UNIX_TIMESTAMP(ts) > (UNIX_TIMESTAMP(CURRENT_TIMESTA
MP) - (60 * 3)) AND FK_webuser_no IS NULL;