Re: [SQL] [ADMIN] Latest transcation
Consider this: Transcation begin ' ' Insert on table x ' ' Trascation end; My client C++ front end is multi-threaded. The above 'transcation' block is in thread -y . I want to know the info. abt' last row inserted into table in this transcation block. Info contains the details like time of insertion of a row and data in that row. I think this sufficely explains what I want. -Anagha -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 4:00 PM To: Anagha Joshi Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Latest transcation On Thu, Jun 19, 2003 at 10:46:22 +0530, Anagha Joshi <[EMAIL PROTECTED]> wrote: > Hi All, > Is there any way to know programatically which is the latest > insert/update occured to a particular table? What are the values which > are inserted/updated to that table? That depends on what you really are trying to do. It sounds like using a sequnce, nextval and currval might work. That could be used to track the latest change in one session. If you try to look at the latest change to a table accross all sessions, things get a bit murky. A more precise description of what you are trying to do might result in some other suggestions. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [GENERAL] Request for advice: Table design
Hi
I realized I made a mistake. :(
The example below should have :
create view all_data as
select ...
The "as" statement was missing.
Here is a real sample of a function allowing a similar effect :
--- sql script ---
--
-- delete old function [must be done before type is dropped]
--
DROP FUNCTION acct_info( TEXT , INTEGER );
--
-- delete old type
--
DROP TYPE acct_info_record;
--
-- return type for acct_info records
--
CREATE TYPE acct_info_record AS (
"Time-Stamp" ABSTIME,
"Acct-Status-Type" TEXT,
"User-Name" TEXT,
"Realm" TEXT,
"Acct-Session-Time" INTEGER,
"Acct-Input-Octets" INTEGER,
"Acct-Output-Octets" INTEGER,
"Called-Station-Id" TEXT,
"Calling-Station-Id" TEXT,
"Acct-Terminate-Cause" TEXT,
"Framed-IP-Address" INET,
"Service-Type" TEXT,
"Framed-Protocol" TEXT,
"Client-IP-Address" INET,
"NAS-IP-Address" INET,
"NAS-Port-Type" TEXT,
"NAS-Port-Id" INTEGER,
"Timestamp" INTEGER,
"Acct-Session-Id" TEXT,
"Acct-Link-Count" SMALLINT,
"Acct-Multi-Session-Id" TEXT,
"Acct-Delay-Time" INTEGER
);
--
-- function to select start and stop records as one data set by "mon"
and year.
--
CREATE FUNCTION acct_info( TEXT , INTEGER ) RETURNS SETOF
acct_info_record AS '
DECLARE
p_mon ALIAS FOR $1;
p_year ALIAS FOR $2;
v_exec TEXT;
rec RECORD;
BEGIN
v_exec := ''SELECT
"Time-Stamp",
"Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Timestamp",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id",
"Acct-Delay-Time"
FROM acct_start_'' || p_year || p_mon ||
'' UNION SELECT
"Time-Stamp",
"Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Timestamp",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id",
"Acct-Delay-Time"
FROM acct_stop_'' || p_year || p_mon ;
FOR rec IN EXECUTE v_exec
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
--
-- check to make sure it works
--
SELECT * FROM acct_info('jun','2003') LIMIT 10;
--
--- end of sql script --
That may not be of as much help for that project, but it was somthing I
realized I could use in one of my applications.
Guy
Dennis Gearon wrote:
wow! Thanks for that info. I'm definitely filing this for use in a
future,near term project.
Guy Fraser wrote:
Hi
As an additional note;
Older data is moved into a seperate table to reduce the number of
records that require regular vacuuming. Since the tables would
contain similar data it is simple to use union selections in a view
with an additional column to indicate which table the data comes
from. Using a view that combines the data from the two tables using a
union, the data will appear to be comming from a single table. This
method make archival access transparent.
I have a realtime data collection system that I built. The data is
put into tables on a yearly and monthly basis on the fly and new
tables are created as needed. I use a union to join tables to access
the data over several months. I just thought of a new idea, I am
going to write a function to join the tables required over a timespan
- but that's another story.
Two tables are easy to join with a union :
{if the column types are exactly matched}
create view all_data
select *,'current_data'::text as data_table from current_data ...
union
select *,'archive_data'::text from archive_data ...
;
The last column will indicate the data's origin.
Now to see all the data :
select * from all_data ;
Thats about it, using this method allows the "dynamic" table to small
for quick maintenace and operation, while the "static" table needs
less maintenace so it can be large with out the penalties incurred by
frequent maintenace.
Guy
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] is this possible in plpgsql ?
I have 1 function in pg 7.3.2. txt string have 1,2,3,.. and i would like to be similar like if 3 in (1,2,3..) then, but IF 3 in (||txt||) THEN doesn't work. There is possible to this or not ? CREATE OR REPLACE FUNCTION rek2(INT8) RETURNS text AS ' DECLARE i INT8; BEGIN txt := ; for i in 1 .. 10 loop txt := txt||i||'',''; END loop; txt := txt||''0''; IF 3 in (||txt||) THEN raise notice ''yep''; END IF; RETURN txt; END; ' LANGUAGE 'plpgsql'; -- "Nieobecni nie mają racji" Marcin Winkler ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is this possible in plpgsql ?
On Thursday 19 Jun 2003 1:38 am, Marcin Winkler wrote:
> I have 1 function in pg 7.3.2.
>
> txt string have 1,2,3,.. and i would like to be similar like if 3 in
> (1,2,3..) then, but IF 3 in (||txt||) THEN doesn't work.
> There is possible to this or not ?
> txt := ;
> for i in 1 .. 10 loop
> txt := txt||i||'','';
> END loop;
> txt := txt||''0'';
> IF 3 in (||txt||) THEN
> raise notice ''yep'';
Try something like:
query:=''SELECT 3 in ('' || txt || '')''
EXECUTE query;
Then check the result for true/false
--
Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [JDBC] maxconnection
On 17/06/2003 10:22 zhuj wrote: hi,all: I want to constraint the maximum number of concurrent connections to 25 in postgres jdbc driver. There are no methods for this change. How would i do? The simplest way is to use a connection pool. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] Latest transcation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 19 June 2003 04:41, Anagha Joshi wrote: > My client C++ front end is multi-threaded. The above 'transcation' block > is in thread -y . > I want to know the info. abt' last row inserted into table in this > transcation block. > Info contains the details like time of insertion of a row and data in > that row. > > I think this sufficely explains what I want. > There are some things you can do at the application level to record what was done in a seperate thread or even process. With threads, you can use shared variables and semaphores. With processes, you can use some form of IPC or shared memory. If you decide to go at an application layer, it is beyond the scope of the ADMIN list, and better suited for a discussion with your peers in that language and environment. If you go for a server-side solution (because the application doesn't know what was last inserted and when), then you'll want to use triggers as I described in a previous posting. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+8cOoWgwF3QvpWNwRAmGkAJ4pE8Eb9V/kiyBFqLCqr/2nNqA3HwCg0PE2 2TCK7YF50MKLwbUurS1aqlY= =hvKY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Getting one row for each subquery row...?
Hi All, here are my three tables. I wold like to list them in the following way: I need all the columns from t_stockchanges, and one field for the productgroup the t_stockchanges.productid belongs to. But one product can belong to many groups, so i would need one row for each group for each product. My bad query is: DB=# select t_stockchanges.productid, (select name from t_productgroups where id=(select productgroupid from t_prod_in_pgr where productid=t_stockchanges.productid)) as pgroup from t_stockchanges; ERROR: More than one tuple returned by a subselect used as an expression. DB=# --- Yes, this is absolutelly true, but I would like postgres to give me all the tuples found. How can I ask him to do so? Thank you, -- Csaba --- - Table "public.t_stockchanges" Column | Type | Modifiers ---+--+ - id| integer | not null stockid | integer | not null productid | integer | not null changeid | integer | not null quantity | double precision | not null date | character(19)| not null purchaseprice | double precision | not null correction| double precision | not null userid| integer | not null time | character(19)| default to_char(now(), '.mm.dd hh:mi:ss'::text) prooftype | character(10)| not null default '' proofid | integer | default 0 Indexes: t_stockchanges_pkey primary key btree (id), t_stockchanges_date btree (date), t_stockchanges_productid btree (productid) --- - Table "public.t_productgroups" Column| Type | Modifiers -+---+--- id | integer | not null name| character varying(30) | not null description | character varying | root| boolean | Indexes: t_productgroups_pkey primary key btree (id) --- - Table "public.t_prod_in_pgr" Column | Type | Modifiers +-+--- productgroupid | integer | not null productid | integer | not null --- - --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.489 / Virus Database: 288 - Release Date: 2003. 06. 10. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting one row for each subquery row...?
Dnia 2003-06-20 07:12, Użytkownik Együd Csaba napisał: Hi All, here are my three tables. I wold like to list them in the following way: I need all the columns from t_stockchanges, and one field for the productgroup the t_stockchanges.productid belongs to. But one product can belong to many groups, so i would need one row for each group for each product. My bad query is: DB=# select t_stockchanges.productid, (select name from t_productgroups where id=(select productgroupid from t_prod_in_pgr where productid=t_stockchanges.productid)) as pgroup from t_stockchanges; ERROR: More than one tuple returned by a subselect used as an expression. DB=# --- Yes, this is absolutelly true, but I would like postgres to give me all the tuples found. How can I ask him to do so? Thank you, -- Csaba Sure, use "limit 1" in a subquery. "limit" and "offset" are well described in Postgresql documentation. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting one row for each subquery row...?
Dnia 2003-06-20 07:12, Użytkownik Együd Csaba napisał: Hi All, here are my three tables. I wold like to list them in the following way: I need all the columns from t_stockchanges, and one field for the productgroup the t_stockchanges.productid belongs to. But one product can belong to many groups, so i would need one row for each group for each product. My bad query is: DB=# select t_stockchanges.productid, (select name from t_productgroups where id=(select productgroupid from t_prod_in_pgr where productid=t_stockchanges.productid)) as pgroup from t_stockchanges; ERROR: More than one tuple returned by a subselect used as an expression. DB=# --- Yes, this is absolutelly true, but I would like postgres to give me all the tuples found. How can I ask him to do so? Thank you, -- Csaba Sorry, if you want all combination of grups and products, you need to rewrite your query: select t_stockchanges.productid, t_productgroups.name as pgroup from t_stockchanges join t_prod_in using (productid) join t_productgroups on (id=productgroupid) or something like this. Tomasz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
