[SQL] Arrays, types and prodedures
Hi
I am having trouble passing arrays of types to a store procedure
I have the following type
CREATE TYPE IndexElement AS (
keyname text,
keytype integer
);
and the header for the store prodedure is as follows:
CREATE OR REPLACE FUNCTION doIndexGroupCount(
indexs IndexElement[],
customerid INTEGER ,
logic INTEGER )
but when I try and call it using the following code
SELECT * from doIndexGroupCount(ARRAY[('dog', 1),('cat', 1)],10,0);
I get and error
ERROR: could not find array type for data type record
What am I missing?
Chris
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Arrays, types and prodedures
hello
2008/7/25 Chris Hoy <[EMAIL PROTECTED]>:
>
>
> Hi
>
> I am having trouble passing arrays of types to a store procedure
> I have the following type
>
>CREATE TYPE IndexElement AS (
>keyname text,
>keytype integer
>);
>
> and the header for the store prodedure is as follows:
>
> CREATE OR REPLACE FUNCTION doIndexGroupCount(
>indexs IndexElement[],
>customerid INTEGER ,
>logic INTEGER )
>
> but when I try and call it using the following code
>
>SELECT * from doIndexGroupCount(ARRAY[('dog', 1),('cat', 1)],10,0);
>
> I get and error
>
>ERROR: could not find array type for data type record
>
> What am I missing?
>
casting
postgres=# select ARRAY[('dog', 1),('cat', 1)]::indexelement[];
array
---
{"(dog,1)","(cat,1)"}
(1 row)
regards
Pavel Stehule
> Chris
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres time zone settings(time difference in Server and client)
Hai all, I have database running on server. I am using python to run my application.postgres client is running on the clients .All clients are connected to a single database running on server. Each time application starts ,the login time is taken from server using the query """select CAST ( timeofday() AS timestamp""" But the problem the time which I got is different from that of server machine . I Think the postgres client is adding some values to the server time. How can I solve this .Is there any configuration setting for client to solve this?.In which location client's configuration file is stored( I am using Linux(Debian )?. here the result I got: The correct time in server: 2008-07-25 14:16:54 But What i got from client (with database cursor of sever database): 2008-07-25 19:46:33.46 (difference of more than 5 Hr ) Where this difference come? any one know the answer pls help me. Thanks in advance Anoop
Re: [SQL] postgres time zone settings(time difference in Server and client)
Anoop G wrote: Hai all, I have database running on server. I am using python to run my application.postgres client is running on the clients .All clients are connected to a single database running on server. Each time application starts ,the login time is taken from server using the query """select CAST ( timeofday() AS timestamp""" But the problem the time which I got is different from that of server machine . I Think the postgres client is adding some values to the server time. How can I solve this .Is there any configuration setting for client to solve this?.In which location client's configuration file is stored( I am using Linux(Debian )?. 1. If you have a recent version of PostgreSQL use clock_timestamp() 2. Why are you using timestamp without timezone? SELECT clock_timestamp(); clock_timestamp 25/07/2008 10:21:58.464055 BST SET timezone='EST'; SELECT clock_timestamp(); clock_timestamp 25/07/2008 04:22:19.584367 EST -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres time zone settings(time difference in Server and client)
2008/7/25 Anoop G <[EMAIL PROTECTED]>:
>
> Hai all,
>
>I have database running on server. I am using python to run my
> application.postgres client is running on the clients .All clients are
> connected to a single database running on server. Each time application
> starts ,the login time is taken from server using the query
> """select CAST ( timeofday() AS timestamp"""
>
> But the problem the time which I got is different from that of server
> machine . I Think the postgres client is adding some values to the server
> time. How can I solve this .Is there any configuration setting for client to
> solve this?.In which location client's configuration file is stored( I am
> using Linux(Debian )?.
>
> here the result I got:
>
> The correct time in server: 2008-07-25 14:16:54
>
> But What i got from client (with database cursor of sever database):
>2008-07-25 19:46:33.46 (difference of more than 5 Hr ) Where this
> difference come?
>
> any one know the answer pls help me.
>
> Thanks in advance
>
> Anoop
>
>
>
>
server localtime is correct ?
/etc/localtime ...
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
Re: [SQL] postgres time zone settings(time difference in Server and client)
Hai all, my postgres version is PostgreSQL 8.1.8 didn't support clock_timestamp() regards: Anoop
[SQL] A table with two names or table alias
Can I create a table name alias? What i want is to access the same table with two different names. An example: I've the table CITY with fields code and name. I want to know if i can create an alternative name to table CITY, like LOCATION, to make possible these two sentences: insert into CITY values (10, 'New York'); insert into LOCATION values (11, 'Paris') Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] A table with two names or table alias
am Fri, dem 25.07.2008, um 14:12:47 +0200 mailte Tk421 folgendes: >Can I create a table name alias? > >What i want is to access the same table with two different names. An > example: > >I've the table CITY with fields code and name. I want to know if i > can create an alternative name to table CITY, like LOCATION, to make > possible these two sentences: > > insert into CITY values (10, 'New York'); > insert into LOCATION values (11, 'Paris') > >Thank you. You can create a VIEW, and if you want to insert data into the view, you can create rules for that. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PERSISTANT PREPARE (another point of view)
Craig Ringer wrote:
Milan Oparnica wrote:
I found this link from IBM DB2 developers showing why PERSISTENT
PREPARE is a good idea and how could it be implemented.
[snip]
NONE OF POPULAR SQL DBMS (Oracle, MS SQL, MySQL, Postgre, INTERBASE,
FIREBIRD) HAVE THIS FEATURE.
WHY ?
I suspect that people tend to use SQL or PL/PgSQL stored procedures
instead. I'm not 100% sure SQL functions cache their query plans, but I
know PL/PgSQL does.
Exactly what is gained by the use of persistent prepare over the use of
a stored procedure?
Its easier to write and call prepared statements then SP's.
When writing, you just create parameters and SQL body of the statement.
When calling from the application or report engine, all you have to do
is "EXEC ()".
In case of SP's written as SET OF CustomDataType it's pretty easy to
call ("SELECT * FROM ()) but its hard to maintain
CutomDataType when changing table structures.
In case of SP's written using CURSORS calling convention is not simple.
BEGIN;
SELECT ('cursor_name', );
FETCH ALL FROM cursor_name;
END;
This usually requires using more complicating ways to define source
recordsets in reports designers. I'm also not sure how cursors use
indexes of the tables. I've run some tests on several millions records
with and without indexes and found out that there was no significant
difference in performance. Plus, for now, CURSORS remain READ-ONLY in PG.
There is one thing more. Prepared statements could be used as table-type
(or view-type) datasource. That means we could run prepared statements
over other prepared statements accepting same parameters. This makes
possible creating complex "low-level" BASE queries and dozens of
reporting queries that rely on them. Something like subquery structures:
SELECT A.Fld1,B.Fld2
FROM
(SELECT Fld1 FROM Table1 WHERE xxx) A INNER JOIN Table2 B ON A.Fld1=B.Fld2
WHERE yyy
What would the interface to the feature be through database access
drivers like JDBC? Explicit PREPARE GLOBAL or similar, then invocation
with EXECUTE ?
They could remain the same. If possible, when calling EXECUTE PG
could try local-then global (or reverse) PREPARED STATEMENTS pool, and
run the statement that was first found. This is greatly simplified. It
would require much detailed planning if this option is to be built in PG.
How would users using increasingly common layers like Hibernate/JPA use it?
I don't now, I never used Hibernate/JPA. Somebody could speak for that.
Making PERSISTANT PREPARE statement available in PG doesn't mean that
everybody must use it.
I'm also curious about how you'd address the possible need for periodic
re-planning as the data changes, though AFAIK SQL functions suffer from
the same limitation there.
Perhaps there could be several ways. It could be implemented within
VACUUM procedure. It could re-plan all global prepared statements
according to new circumstances. There should also be a manual
re-planning command of specific statement maybe even with passing
parameters to it so the optimization could be even more efficient.
I guess I personally just don't understand what the point of the
persistent prepare feature you describe is. However, this post that you
linked to:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00867.php
actually describes a query plan cache, rather than persistent prepare.
The post assumes the app will explicitly manage the cache, which I'm not
sure is a good idea, but I can see the point of a plan cache. There
might be some heuristics Pg could use to decide what to cache and to
evict (planner time cost vs memory use, frequency of use, etc) so the
app doesn't have to know or care about the plan cache. However, I'm not
too sure how you'd match an incoming query to a cached plan, and
determine that the plan was still valid, with enough speed to really
benefit from the plan cache. Then again, I don't know much about Pg's
innards, so that doesn't mean much.
Tom Lane responded to that post to point out some of the complexities:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php
--
Craig Ringer
I think persistent prepare has to do something with global caching
plans. Preparing statement actually prepares a plan of its execution
giving us the possibility to execute it. With persistent prepare
statement it somehow these plans have to go from local to global. I
agree they shouldn't be managed by app itself. App can only create
requests for creation, modification, execution and removing of such object.
This post is about a point of view. I know we can use stored procedures
and views and other good features of PG to manipulate all data we need.
What I aim is: it can be done in more simple and efficient way with
PERSISTENT PREPARE implementation. Let me point the key benefits:
- huge number of database developers could use this technique without
getting involved with CURSORS and CUSTOM DATA TYPE SETS.
Thi
Re: [SQL] Query prepared plan
Good morning, May I know the "commands" to . show current session's prepared plans . see the definition of a prepared plan E.g., psql> PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); (1) Similar to "\dt", I want to see "fooplan" (2) Similar to "\d tableName", how to see the plan def? Somebody know about how to find prepared query plan through command line? Thanks a lot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] A table with two names or table alias
There are no synonyms in Postgres (like in Oracle). To implement what you want, you need to use views and rules. From Postgres docs: "Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables." Read docs on views and rules. HTH, Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tk421 Sent: Friday, July 25, 2008 8:13 AM To: pgsql-sql@postgresql.org Subject: [SQL] A table with two names or table alias Can I create a table name alias? What i want is to access the same table with two different names. An example: I've the table CITY with fields code and name. I want to know if i can create an alternative name to table CITY, like LOCATION, to make possible these two sentences: insert into CITY values (10, 'New York'); insert into LOCATION values (11, 'Paris') Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query prepared plan
Emi Lu <[EMAIL PROTECTED]> writes: > Somebody know about how to find prepared query plan through command line? PREPARE fooplan(...) EXPLAIN EXECUTE fooplan(...) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PERSISTANT PREPARE (another point of view)
Jeff Williams wrote: You can do this with cursors, but I'm not sure if you still get the query caching? I can do it with cursors, I don't get query cache but that is for now not an issue. Issue is performance. Although using cursors show better performance over using SET OF user defined data types, it still swallows huge amount of memory. Here are the results of a query (I post the query at the end just for sake of complexity example) running on database of 1.6 GB of data and around 5 million of records: TimeApprox RAM usageSwaping PREPARE Statement 5 min. 300 MB None SP with SET OF 16 min. 800 MB Heavy SP with cursor 9 min. 800 MB Medium Environment: Run on Query Tool of pgAdmin III. It does no data modification. Number of clients: 1 PostgreSQL version 8.3 Processor P4 2.66 GHZ RAM memory 1 GB. There where no other active programs or demanding services running in the background. I presume adding more RAM would increase performance but we usually have 10 to 20 clients working at a time. I guess that would put some load. Besides, fetching records from cursor type SP requires transactional approach in calling convention (BEGINstatment...END). At this moment I don't know if this can be done in our main report designer tool - Business Objects (former Crystal Reports). Best regards, Milan Oparnica Here is the text of SQL statement: Note: All fields in join are indexed and every where field is also indexed except boolean fields. CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo timestamp without time zone) RETURNS refcursor AS $$ BEGIN OPEN $1 FOR SELECT 10 AS Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc, U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8) AS Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol, SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena, U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol, SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID = SUN.SasUlID WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel <> false) AND U.DatDoc <= InDatDo UNION ALL SELECT 20 AS Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc, U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,U.Link, SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol, SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,(U.DTStamp -(20*case when DT.Rank > 5 then -1 else 0 end)) AS Tabela, U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID = SUN.SasUlID WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo UNION ALL SELECT 28 AS Poredak,U.UlID,U.DatDoc,False AS FKomis,DT.FSCObrac,DT.FNivelTot, DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena, U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU, SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited, SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID = SUN.SasUlID WHERE U.DatDoc <= InDatDo AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel <> false) AND U.DocTip = 31 AND((SK.RacTipSKL = 3 AND(DT.FMP <> false)) OR(SK.RacTipSKL <> 3 AND(DT.FVP <> false))) UNION ALL SELECT 30 AS Poredak,0 AS UlID,O.DatDoc,False AS FKomis,False,False,False,2 AS DocTip, 0 AS SasUlID,SO.ElID,-SUM(SO.Kol) AS Kol,0 AS NCena,0 AS TKol, 0 AS SCena,SK.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,0 AS Link, 0 AS FCena,0 AS Popust,0 AS Marza,0 AS MCena,0 AS MPorez,0
Re: [SQL] PERSISTANT PREPARE (another point of view)
Hello this is near my implemenation of stored procedures - it's not far from your view on prepared statements. There result of any unbound select is directly forwarded to client - there is little bit different syntax, but idea is same create procedure foo(params) as $$ begin select a, b, c from tab1 -- unbound select select end; and you can call with CALL statement. Curent implementation of SRF function in plpgsql sn't eficient for big results - it needs materialised table in memory. Pipeline Oracle's functions are better, but we are limited one thread architecture. regards Pavel Stehule 2008/7/25 Milan Oparnica <[EMAIL PROTECTED]>: > Jeff Williams wrote: > >> You can do this with cursors, but I'm not sure if you still get the >> query caching? >> > > I can do it with cursors, I don't get query cache but that is for now not an > issue. > > Issue is performance. > > Although using cursors show better performance over using SET OF user > defined data types, it still swallows huge amount of memory. > > Here are the results of a query (I post the query at the end just for sake > of complexity example) running on database of 1.6 GB of data and around 5 > million of records: > >TimeApprox RAM usageSwaping > PREPARE Statement 5 min. 300 MB None > SP with SET OF 16 min. 800 MB Heavy > SP with cursor 9 min. 800 MB Medium > > Environment: > > Run on Query Tool of pgAdmin III. It does no data modification. > Number of clients: 1 > PostgreSQL version 8.3 > Processor P4 2.66 GHZ > RAM memory 1 GB. > > There where no other active programs or demanding services running in the > background. > > > I presume adding more RAM would increase performance but we usually have 10 > to 20 clients working at a time. I guess that would put some load. > > Besides, fetching records from cursor type SP requires transactional > approach in calling convention (BEGINstatment...END). At this moment I > don't know if this can be done in our main report designer tool - Business > Objects (former Crystal Reports). > > Best regards, > > Milan Oparnica > > > Here is the text of SQL statement: > > Note: All fields in join are indexed and every where field is also indexed > except boolean fields. > > CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo timestamp > without time zone) RETURNS refcursor AS $$ > BEGIN >OPEN $1 FOR >SELECT 10 AS > Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc, > U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8) AS > Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol, > SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena, > U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol, > SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS > NivelEdited, > SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, > U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote > FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip) > INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON > U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID = > SUN.SasUlID > WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel > <> false) AND U.DatDoc <= InDatDo > UNION ALL > SELECT 20 AS > Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc, > U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS > NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), > SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,U.Link, > > SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol, > SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited, > SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS > LastSCena,SK.RacTipSKL,(U.DTStamp -(20*case when DT.Rank > 5 then -1 > else 0 > end)) AS Tabela, > U.DopSklID,DT.FPrenos,SK.FRemote > FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip) > INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON > U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID = > SUN.SasUlID > WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND > NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo > UNION ALL > SELECT 28 AS Poredak,U.UlID,U.DatDoc,False AS > FKomis,DT.FSCObrac,DT.FNivelTot, > DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS > NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8), > SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena, > > U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU, > SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited, > SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL, > U.DTStamp AS Tabela,U.DopSklID,D
Re: [SQL] PERSISTANT PREPARE (another point of view)
1. like the idea because right now I'm having to track which prepared statement (sets) are in which session. using xcache to track this, but little bit dangerous. could query the database first but the whole idea is to take a load off the db. 2. perhaps "global" could mean simply that the definition is global - if called for session and not exist in session, then session prepares it first from the global def. there would need to be a back reference in case the global def was changed or dropped. 3. don't understand your statement about how prepared statements can be used as subqueries - you mean something like select .. from tab1 t1 where t1.col1 in( exec prep1(..) ) or exactly what? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Test if a table is present
How can I test if a table is present in database? Regards, Ole
Re: [SQL] Test if a table is present
you can find it throuh a query like this select tablename from pg_tables where tablename = 'tbl_updated_status_master'; regards: Anoop
Re: [SQL] Test if a table is present
On Sat, 26 Jul 2008, "Ole Ekerhovd" <[EMAIL PROTECTED]> writes: > How can I test if a table is present in database? SELECT TRUE FROM information_schema.tables WHERE table_name = '' AND table_schema = '' BTW, this query is portable across database systems supporting information schema, which is an almost defacto standard. Regards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
