Re: [SQL] Crosstab function
bandeng wrote: hello guys, I want to use crosstab function but that function it doesnt exist. my version is 7.3 so how do i get the tablefunc.sql from postgre contrib? i saw in ver 8 win32 is checkable. Is it not in the contrib/ directory? Or did you install from a package of some sort (and if so, what sort). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [despammed] [SQL] Crosstab function
am 06.04.2005, um 13:55:35 +0700 mailte bandeng folgendes: > hello guys, > > I want to use crosstab function but that function it doesnt exist. my > version is 7.3 so how do i get the tablefunc.sql from postgre contrib? apt-get install postgresql-contrib Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
Title: CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
--Oracle
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA IN VARCHAR2
)
RETURN VARCHAR2
IS
v_sql VARCHAR2(2000);
alert_mesg VARCHAR2(32767);
IN_VAR1 VARCHAR2(10);
IN_VAR2 VARCHAR2(10);
V_COUNT NUMBER;
v_cursorid NUMBER;
v_dummy INTEGER;
v_source VARCHAR2(100);
BEGIN
v_cursorid := DBMS_SQL.OPEN_CURSOR;
v_sql := 'SELECT A1, A2, count(*) FROM A group by A1,A2';
--Parse the query.
DBMS_SQL.PARSE(v_cursorid, v_sql, DBMS_SQL.V7);
--Define output columns
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 1, IN_VAR1, 10);
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 2, IN_VAR2, 10);
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 3, V_COUNT);
--Execute dynamic sql
v_dummy := DBMS_SQL.EXECUTE(v_cursorid);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursorid) = 0 then
exit;
END IF;
DBMS_SQL.COLUMN_VALUE(v_cursorid,1,IN_VAR1);
DBMS_SQL.COLUMN_VALUE(v_cursorid,2,IN_VAR2);
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursorid);
RETURN alert_mesg;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursorid);
RETURN 'No troubleshooting information at this time.'|| SQLERRM;
END MYCURSOR;
/
SHOW ERROR
=
--Oracle
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA IN VARCHAR2
)
RETURN VARCHAR2
IS
alert_mesg VARCHAR2(32767);
IN_VAR1 VARCHAR2(10);
IN_VAR2 VARCHAR2(10);
CURSOR MYCUR IS SELECT A1, A2, count(*) FROM A group by A1,A2;
BEGIN
FOR rec IN MYCUR LOOP
IN_VAR1 := rec.A1;
IN_VAR2 := rec.A2;
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
RETURN alert_mesg;
EXCEPTION
WHEN OTHERS THEN
RETURN 'No troubleshooting information at this time.'|| SQLERRM;
END MYCURSOR;
/
SHOW ERROR
--PostgreSQL
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA VARCHAR
)
RETURNS VARCHAR
AS
$$
DECLARE
_record RECORD;
alert_mesg VARCHAR(2000);
IN_VAR1 VARCHAR(10);
IN_VAR2 VARCHAR(10);
BEGIN
alert_mesg := '';
--Define output columns
FOR _record IN SELECT A1, A2, count(*) FROM A group by A1,A2
LOOP
IN_VAR1 := _record.A1;
IN_VAR2 := _record.A2;
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
RETURN alert_mesg;
--EXCEPTION
-- WHEN OTHERS THEN
-- RETURN 'No troubleshooting information at this time.';
END;
$$ LANGUAGE plpgsql;
--PostgreSQL
CREATE OR REPLACE FUNCTION MYCURSOR (VARINA VARCHAR) RETURNS VARCHAR AS $$
declare
--cur1 cursor is select A1, A2 from A;
cur1 refcursor;
cid integer;
_A1 varchar (10) ;
_A2 varchar (10) ;
alert_mesg VARCHAR(2000) := '';
BEGIN
--open cur1;
OPEN cur1 FOR execute('select * from A');
loop
fetch cur1 into _A1, _A2;
if not found then
exit ;
end if;
alert_mesg := alert_mesg||rpad(_A1,20)||rpad(_A2,20);
end loop;
close cur1;
return alert_mesg;
END;
$$ LANGUAGE plpgsql
--
Dinesh Pandey
Sr. Software Engineer
[SQL] DROP TYPE without error?
Hi, Since it is not possible to use CREATE OR REPLACE TYPE, is there a way of using DROP TYPE on a non-existing type, without causing the entire script to abort? It may sound crazy to ask for this, but it could be really useful in my case, where Pl/Pgsql and Pl/Perl code is being generated automatically, based on data found in a database. Thanks - Philippe Lang ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] createlang 'pltclu' with postgreSQL.
Title: createlang 'pltclu' with postgreSQL. Hi, I have already installed Postgres 8.0.1. I am able to use pl/pgsql language but...I want to use language 'pltclu' with postgreSQL. Its unable to find "pltcl". I am getting this error. $ createlang pltclu testdb ERROR: could not access file "$libdir/pltcl": No such file or directory createlang: language installation failed: ERROR: could not access file "$libdir/pltcl": No such file or directory Where this file "$libdir/pltcl" can be found I have installed "tk-8.4.9-sol9-sparc-local.gz" and "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. Any othe configuration required Regards Dinesh Pandey
[SQL] TIMESTAMP / summertime
Hello,
I *detest* British summertime. This year it took me two days to adjust.
Now I am realizing that my program might need some adjusting too:
Joking aside, I need some advice regarding TIMESTAMP colums and I can't
quite get my head round this at the moment:
I created a table TRANSAKTION with a TIMESTAMP column without qualifying
"with/without time zone".
My understanding is that this is equivalent to "TIMESTAMP without time
zone"? (I am using Postgres 7.4.)
I am accessing the database via a Java client program. The DB access
code is generated by an O/R mapper. Client and server are in the same
timezone.
One of the things I need to do select records from TRANSAKTION, which
fall within a certain time period, specified in days: e.g. 1st Mar 2005
to 31st Mar 2005. In other words, I want to grab TRANSAKTIONs >= 1 Mar
00:00 and < 1 Apr 00:00.
The generated WHERE clause is:
WHERE (TRANSAKTION.THE_TIME>={ts '2005-03-01 00:00:00.0'} AND
TRANSAKTION.THE_TIME<{ts '2005-04-01 01:00:00.0'})
Should it be '2005-04-01 00:00:00.0' or 2005-04-01 01:00:00.0' ??
Also, in autumn, when the clocks go back, I need to be able to
distinguish between the two double hours.
Sorry, if I sound confused. Unfortuantely, this is what I am ;-)
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] createlang 'pltclu' with postgreSQL.
On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote: > > I have already installed Postgres 8.0.1. How did you install PostgreSQL? Did you build it from source? > $ createlang pltclu testdb > ERROR: could not access file "$libdir/pltcl": No such file or directory > createlang: language installation failed: ERROR: could not access file > "$libdir/pltcl": No such file or directory If you built from source, did you run configure with --with-tcl? > I have installed "tk-8.4.9-sol9-sparc-local.gz" and > "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. > Any othe configuration required When I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown): configure --with-tcl --with-tclconfig=/usr/local/lib Try rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] createlang 'pltclu' with postgreSQL.
Hi Mike, Have you installed tcl and tk before popstgres installation and set some PATH for that before installing postgres.Because I installed "tk-8.4.9-sol9-sparc-local.gz" and "tcl-8.4.9-sol9-sparc-local.gz" after postgres installation.Can I use "configure --with-tcl --with-tclconfig=/usr/local/lib" this command now?? What is "/usr/local/lib" - Path of TCL or Path of Postgres installation??ThanksDinesh Pandey -Original Message-From: Michael Fuhr [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 06, 2005 9:14 PMTo: Dinesh PandeyCc: 'PostgreSQL'Subject: Re: [SQL] createlang 'pltclu' with postgreSQL.On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote:>> I have already installed Postgres 8.0.1.How did you install PostgreSQL? Did you build it from source?> $ createlang pltclu testdb> ERROR: could not access file "$libdir/pltcl": No such file or> directory> createlang: language installation failed: ERROR: could not access> file> "$libdir/pltcl": No such file or directoryIf you built from source, did you run configure with --with-tcl?> I have installed "tk-8.4.9-sol9-sparc-local.gz" and> "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command.> Any othe configuration requiredWhen I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown):configure --with-tcl --with-tclconfig=/usr/local/libTry rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu.--Michael Fuhrhttp://www.fuhr.org/~mfuhr/
Re: [SQL] createlang 'pltclu' with postgreSQL.
1. I installed with compiled package using "pkgadd -d postgresql-8.0.1-sol9-sparc-local" 2. I haven't used --with-tcl? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Wednesday, April 06, 2005 9:14 PM To: Dinesh Pandey Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote: > > I have already installed Postgres 8.0.1. How did you install PostgreSQL? Did you build it from source? > $ createlang pltclu testdb > ERROR: could not access file "$libdir/pltcl": No such file or > directory > createlang: language installation failed: ERROR: could not access > file > "$libdir/pltcl": No such file or directory If you built from source, did you run configure with --with-tcl? > I have installed "tk-8.4.9-sol9-sparc-local.gz" and > "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. > Any othe configuration required When I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown): configure --with-tcl --with-tclconfig=/usr/local/lib Try rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
FW: [SQL] createlang 'pltclu' with postgreSQL.
Unable to run "createlang pltcl testdb" [1]. I tried with this command ./configure --with-tcl --with-tclconfig=/usr/local/lib . checking whether the C compiler still works... yes checking how to run the C preprocessor... gcc -E checking whether to build with Tcl... yes . . checking for tclsh... /usr/local/bin/tclsh checking for tclConfig.sh... /usr/local/lib/tclConfig.sh checking tcl.h usability... yes checking tcl.h presence... yes checking for tcl.h... yes [2]. And then createlang pltcl testdb ERROR: could not access file "$libdir/pltcl": No such file or directory createlang: language installation failed: ERROR: could not access file "$libdir/pltcl": No such file or directory Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dinesh Pandey Sent: Wednesday, April 06, 2005 9:30 PM To: 'Michael Fuhr' Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. 1. I installed with compiled package using "pkgadd -d postgresql-8.0.1-sol9-sparc-local" 2. I haven't used --with-tcl? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Wednesday, April 06, 2005 9:14 PM To: Dinesh Pandey Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote: > > I have already installed Postgres 8.0.1. How did you install PostgreSQL? Did you build it from source? > $ createlang pltclu testdb > ERROR: could not access file "$libdir/pltcl": No such file or > directory > createlang: language installation failed: ERROR: could not access > file > "$libdir/pltcl": No such file or directory If you built from source, did you run configure with --with-tcl? > I have installed "tk-8.4.9-sol9-sparc-local.gz" and > "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. > Any othe configuration required When I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown): configure --with-tcl --with-tclconfig=/usr/local/lib Try rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] DROP TYPE without error?
Quoting Philippe Lang <[EMAIL PROTECTED]>:
> Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
> of using DROP TYPE on a non-existing type, without causing the entire
> script to abort? It may sound crazy to ask for this, but it could be
> really useful in my case, where Pl/Pgsql and Pl/Perl code is being
> generated automatically, based on data found in a database.
I've got a similar request for other objects that do/do not exist.
Maybe it's just that I got lazy using MSSQL, but it sure was convenient
to have:
IF object_id('WorkTable') IS NULL
CREATE TABLE WorkTable(...
etc.
Given that you cannot just execute an anonymous block of PL/PGSQL code,
where you could do the test AND the create ...
--
"Dreams come true, not free."
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] [GENERAL] Problems with Set Returning Functions (SRFs)
"Otto Blomqvist" <[EMAIL PROTECTED]> writes: > secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); > ERROR: more than one row returned by a subquery used as an expression In 8.0 I think it'd work to do select (x).f1, (x).f2, (x).f3 from (select testpassbyval(1, number1) as x from test) ss; regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] 9.17.5. Row-wise Comparison
In tbl with columns a,b,c,d. I've been using syntax like select a from tbl where (b,c,d) > (1,2,3) to mean select a from t where b>1 and b>2 and d>3 But I see in the manual at: http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735 that only = and <> operators are supported. Does this section of the manual not properly apply to this query? Is the manual in error, or am I not understanding? TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] ar: Command not found.
Title: ar: Command not found. Hi I am getting this error while "make install" of postgres. make[2]: ar: Command not found. make[2]: *** [libpgport.a] Error 127 make[2]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src/port' make[1]: *** [install] Error 2 make[1]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src' make: *** [install] Error 2 Which package need to be installed? Regards Dinesh Pandey -- Dinesh Pandey Sr. Software Engineer Second Foundation (India) Pvt. Ltd. Plot# 52 Industrial Area, Phase II Chandigarh. (India) PH: (O) 0172-2639202, Extn: 233
Re: [SQL] 9.17.5. Row-wise Comparison
"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > I've been using syntax like > select a from tbl where (b,c,d) > (1,2,3) >to mean > select a from t where b>1 and b>2 and d>3 > But I see in the manual at: > http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735 > that only = and <> operators are supported. Does this section of the manual > not properly apply to this query? Is the manual in error, or am I > not understanding? PG's current code acts as you are supposing, but it is broken because it doesn't follow the SQL spec, and we will change it as soon as someone gets around to working on it. The spec says that this syntax implies a column-by-column ordering, essentially if (b > 1) then true else if (b = 1 and c > 2) then true else if (b = 1 and c = 2 and d > 3) then true else false You can find related discussions in the archives from a few months back. The spec's semantics correspond exactly to the sort ordering of a multiple-column btree index, and so there are good reasons why we'd want to provide that behavior even if it weren't mandated by the spec. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] ar: Command not found.
Title: ar: Command not found. In Solaris "ar" command is in "/usr/ccs/bin" directory. Thanks Dinesh Pandey From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dinesh PandeySent: Thursday, April 07, 2005 10:21 AMTo: 'PostgreSQL'Subject: [SQL] ar: Command not found. Hi I am getting this error while "make install" of postgres. make[2]: ar: Command not found. make[2]: *** [libpgport.a] Error 127 make[2]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src/port' make[1]: *** [install] Error 2 make[1]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src' make: *** [install] Error 2 Which package need to be installed? RegardsDinesh Pandey--Dinesh Pandey Sr. Software EngineerSecond Foundation (India) Pvt. Ltd.Plot# 52Industrial Area, Phase IIChandigarh. (India)PH: (O) 0172-2639202, Extn: 233
