DevAdmin

Hi , We do have unnest operation in PostgreSQL for fetching the records from any arrary. Step 1 ====== Create a sample UDT as below. create type t_type as (a varchar); Step 2 ====== Create a sample procedure as below. create or replace function fn_test() return t_type[] is v t_type[]; begin raise notice '%',array(select ename from emp); select array(select row(ename) from emp)::varchar[] into v; return v; end; Step 3 ====== edb=# select unnest(fn_Test()); NOTICE:  {SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER}   unnest   ----------  (SMITH)  (ALLEN)  (WARD) --Dinesh

Hi , Here are the steps, how to configure the JDBC with PostgreSQL/PostgresPlus. JDBC PostgreSQL =============== Step 1 ====== Download the postgreSQL jdbc diver. Step 2 ====== cp the file location to /opt/PostgreSQL/9.1/lib Step 3 ====== Create the following JavaTest.java @/opt/PostgreSQL/9.1 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JavaTest { public static void main(String[] args) throws ClassNotFoundException, SQLException{ Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "postgres"); PreparedStatement ps = con.prepareStatement("SELECT RELNAME,RELFILENODE FROM PG_CATALOG.PG_CLASS"); //String oldestDate = "20120101"; //ps.setString(1, oldestDate); System.out.println(...

Hi , If you are getting the above ERROR message while configuring PL/PYTHON module, then please use the below steps to configure in PostgreSQL. We need to use PYTHON 3.2 Version due to some bugs in earlier version. Step 1 ===== Download Link: http://www.activestate.com/activepython/downloads/thank-you?dl=http://downloads.activestate.com/ActivePython/releases/3.2.2.3/ActivePython-3.2.2.3-linux-x86.tar.gz" Step 2 ===== -bash-4.1$ export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH -bash-4.1$ export PYTHONHOME=/opt/ActivePython-3.2/ -bash-4.1$ export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib/ Step 3 ===== -bash-4.1$ ./pg_ctl -D ../data/ stop -mf waiting for server to shut down.... done server stopped -bash-4.1$ ./pg_ctl -D ../data/ start server starting Step 4 ===== -bash-4.1$ ./psql -p 5434 postgres Password: psql.bin (9.1.4) Type "help" for help. postgres=# CREATE LANGUAGE plpython3u; CREATE LANGUAGE postgres=# Step 5 ===== postgres=...

Hi , This is the pgpool Prefix option for skipping any statement to go to slave side. Let's consider an example. If we have "Pgpool" Replication and "No Load Balance" Mode.  In this case, we all know that all the SELECT operations goes to Primary Node Only. Ok, then what's case if we use "DECLARE CURSOR -> OPEN -> FETCH -> CLOSE" ? This is also a behvaiour of SELECT. But pgpool will replicate these kind of cases to Slave Node. To avoid this case, we need to use the above /*NO LOAD BALANCE*/ as a pgpool prefix.  Ex:- postgres=#/*NO LOAD BALANCE*/ DECLARE --> OPEN --> FETCH-->CLOSE; It won't cause to replicate (DECLARE/OPEN/FETCH/CLOSE) to slave.  What's the case if i use /*NO LOAD BALANCE*/ CREATE TABLE TEST(T INT)?; Well, Your guess is correct. Pgpool won't replicate this statement as well. Because, When Pgpool sees that prefix, it just ignore the following SQL statement to Slave.  So, be carefu...

Hi , If you are getting the above error in Slony(It's new feature in Slony 2.1 which checks the slony catalogs), then try to execute the below query. select tab_id, tab_relname, tab_nspname,tab_set from  "_Slonyschema".sl_table t where not exists (select 1 from pg_catalog.pg_class c, pg_catalog.pg_namespace n where c.oid = t.tab_reloid and c.relname = t.tab_relname and c.relnamespace = n.oid and n.nspname = t.tab_nspname) If gives any entries then use the following function to fix the Slony catalogs. Select _Slonyschema.updatereloid(<SETID>,<NODEID>); (OR) Use REPAIR CONFIG SLONIK REPAIR CONFIG ( SET ID = 1, EVENT NODE = 2 ); --Dinesh

Hi , Here is my first PL/Java which i have been compiled the documented PL/Java Example. Step 1 ====== bash-4.1$ vi HelloWorld.java -bash-4.1$ pwd /opt/PostgresPlus/9.1AS -bash-4.1$ more HelloWorld.java       package com.mycompany.helloworld;      public class HelloWorld      { public static String helloWorld()          {                 return "Hello World";          }      } Step 2 ====== -bash-4.1$ javac HelloWorld.java  -bash-4.1$ mkdir -p com/mycompany/helloworld/ -bash-4.1$ cp HelloWorld.class com/mycompany/helloworld/ -bash-4.1$ jar cf helloworld.jar com/mycompany/helloworld/HelloWorld.class Step 3 ===== edb=# SELECT sqlj.install_jar('file:///opt/PostgresPlus/9.1AS/hellowor...