Postgres script - for loop two dimension array using array upper and lower

A basic script to loop over an array in psql-

CREATE OR REPLACE FUNCTION func1(n character varying, v character varying)

  RETURNS integer AS

$BODY$

    DECLARE   

       return_code integer;

    BEGIN

       RAISE NOTICE '(%,%)', n, v;          

       return_code := 1;

    RETURN return_code; 

    EXCEPTION 

        WHEN NO_DATA_FOUND THEN           
           RETURN -1;
    END;

    $BODY$

  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func2()

  RETURNS integer AS

$BODY$

    DECLARE      

       return_code integer;

       pairs varchar[][] := array[['key2','val2'],

                            ['key1','val2']];

    BEGIN

        FOR i IN array_lower(pairs, 1) .. array_upper(pairs, 1)

        LOOP

         --RAISE NOTICE '%,%',pairs[i][1]::varchar, pairs[i][2]::varchar;

           PERFORM func1(pairs[i][1]::varchar, pairs[i][2]::varchar);

        END LOOP;

    return 1;

    END;

    $BODY$

  LANGUAGE plpgsql;

SELECT * from func2();

Executing Postgres crosstab query as a prepared statement

I was executing a crosstab query as a prepared statement in Java(in a GWT app) and getting the following error -

PSQLException - Can't use query methods that take a query string on a PreparedStatement.

With some helpful folks from stackflow, I was able to resolve the error with the following code -

String query = "SELECT * FROM crosstab(
                      'SELECT rowid, a_name, value
                       FROM test WHERE a_name = ''att2''
                                    OR a_name = ''att3''
                      ORDER BY 1,2'

) AS ct(row_name text, cat_1 text, cat_2 text, cat_3 text);";

PreparedStatement stat = conn.prepareStatement(query);
ResultSet rs = stat.getResultSet();
//Note that, it is executeQuery() and not executeQuery(query)
stat.executeQuery();
rs = stat.getResultSet();
while (rs.next()) {
    //TODO
}




Thanks!

PostgreSQL crosstab query - Rotate a table about a pivot

An interesting feature of relational databases(postgres in this case) is the ability to rotate the table about a pivot. So if you have data like this-
 id | rowid | key | value
---+------+----+-------
  1 | test1 | key1      | val1
  2 | test1 | key2      | val2
  3 | test1 | key3      | val3
  4 | test1 | key4      | val4
  5 | test2 | key1      | val5
  6 | test2 | key2      | val6
  7 | test2 | key3      | val7
  8 | test2 | key4      | val8

And want to have a result set like this -

rowid | key1 | key2 | key3 | key4
------+------+-----+-----+------
 test1  | val1  | val2  | val3  | val4
 test2  | val5  | val6  | val7  | val8


It can be achieved by a "crosstab" query in a postgres database -



Update: 02/14/2013
The following can be achieved by a crosstab and also by an interesting SQL which I came across here - http://stackoverflow.com/questions/14863985/postgres-crosstab-maybe

Remote access to PostgreSQL Server

Error: "could not connect to Server: Connection refused"

The resolution is a five step process : -

1. Firstly, check if your firewall allows the port (default is 5432) to establish a connection.

2. By default, PostgreSQL does not allow remote incoming connections. This has been implemented in this way, because of security concerns. You need to enable incoming TCP/IP connection requests from the client. This can be done by adding

#
listen_addresses = '*'
#

in the configuration file postgresql.conf. This configuration is valid for postgresql 8.x.

3. You will have to inform Postgresql who can connect to the server. This has to be done by modifying the configuration file pg_hba.conf

host all all 127.0.0.1/32 trust
host all all 192.168.0.0/16 trust

This would allow any user on the local network to access all the databases on the server in "trust" mode.

4. After you have saved the configuration files you will have to restart the server. Remember, these configuration files are read on server startup.

5. Test your config. Go to the command prompt and run the following command.


>psql -h serverIP -U postgresUser -d postgresdb


And, you should be set for remote access to postgresql server.

Connecting to an existing database using TurboGears framework

Your application would need a DB connection to access the database. A connection is how you tell SQLObject to locate your database. SQLObject is an object relational mapper, a software layer that maps data stored in a relational database to an object model in an object-oriented programming language. The connection string resembles a URI in the following format:

scheme://[user[:password]@]host[:port]/database[?parameters]
For instance:
postgres://user:pwd@localhost:5432/db

SQLObject provides a debug mechanism, which can be turned on easily by appending ?debug=True to the end of your database URI in the dev.cfg file.

postgres://user:pwd@localhost:5432/db?debug=True
This would publish the query generated onto the console.
              ..........

Define the existing table Bookmark as a class in model.py:-

class Bookmark(SQLObject):

_fromDatabase = True # the table schema be loaded from the database

BookmarkName = StringCol (dbName="bookmark_name")

ProjectId = IntCol(dbName="project_id")

# dbName is the name of the column in the database, BookmarkName is the name reference in code.
..........
Using the following code in controller.py you should be able to retrieve the second record from the Bookmark table

b=Bookmark.select()
print ":  %s " % b[2].BookmarkName