Re: [SQL] record type
2008/7/10 Marcin Krawczyk <[EMAIL PROTECTED]>:
> Hi. I need to know whether it's possible for a plpgsql function to accept
> record type parameters ? Is there a way to accomplish that ?
> I need to use something like ('1','2','3') as a parameter.
>
> regards
> mk
>
All about record type
http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
section 38.3.4
but, try this ;-)
merlin=# create table your_table(col1 int, col2 varchar(12), col3 int);
CREATE TABLE
merlin=# create or replace function test_1(val_of your_table) returns void
as $$
declare
begin
insert into your_table values(val_of.col1, val_of.col2, val_of.col3);
end;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE FUNCTION
merlin=# select test_1((1,'test',2));
test_1
(1 row)
merlin=# select * from your_table ;
col1 | col2 | col3
--+--+--
1 | test |2
(1 row)
Time: 0.380 ms
--
--
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] record type
Nice thanks a lot.
Niezłe, dzieki.
regards
pozdrowienia
mk
2008/7/11 Pawel Socha <[EMAIL PROTECTED]>:
>
>
> 2008/7/10 Marcin Krawczyk <[EMAIL PROTECTED]>:
>
> Hi. I need to know whether it's possible for a plpgsql function to accept
>> record type parameters ? Is there a way to accomplish that ?
>> I need to use something like ('1','2','3') as a parameter.
>>
>> regards
>> mk
>>
>
> All about record type
>
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
> section 38.3.4
>
>
> but, try this ;-)
>
> merlin=# create table your_table(col1 int, col2 varchar(12), col3 int);
> CREATE TABLE
>
> merlin=# create or replace function test_1(val_of your_table) returns void
> as $$
> declare
> begin
> insert into your_table values(val_of.col1, val_of.col2, val_of.col3);
> end;
> $$ LANGUAGE plpgsql;
> CREATE FUNCTION
>
>
> CREATE FUNCTION
> merlin=# select test_1((1,'test',2));
> test_1
>
>
> (1 row)
>
>
> merlin=# select * from your_table ;
> col1 | col2 | col3
> --+--+--
> 1 | test |2
> (1 row)
>
> Time: 0.380 ms
>
>
>
>
> --
> --
> 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'
[SQL] ESQL CREATE DATABASE
Hello, I am using embedded SQL command for creating the database: EXEC SQL BEGIN DECLARE SECTION; char dbase[32]; EXEC SQL END DECLARE SECTION; ... EXEC SQL CONNECT TO postgres; //the create database cannot be run in transaction block EXEC SQL SET AUTOCOMMIT TO ON; EXEC SQL CREATE DATABASE :dbase; I get an error during compile with ecpg (/usr/bin/ecpg -I../../include -c source_file.ec) ERROR: syntax error at or near ":dbase" *** Error code 3 When I use literal string like: "EXEC SQL CREATE DATABASE my_dbase;" it is compilable and it works. All other SQL commands work with the variables except for the CREATE DATABASE. I am using the package postgresql-devel-8.1.3-12. Any idea? Thank you! Pavel Krejci
[SQL] Rollback in Postgres
Hi all This is a very basic question.can we roll back data after we run a query. I know that a delete within a transaction can be rolled back. But how about independent delete queries??? If i ran a delete statement and lost data...how do i recover. I know that oracle has this provision of rollingback queries. Iam surprised iam not able to find the same in postgres. Sam
Re: [SQL] Rollback in Postgres
On Fri, 2008-07-11 at 11:43 -0400, samantha mahindrakar wrote: > Hi all > This is a very basic question.can we roll back data after we run a > query. > I know that a delete within a transaction can be rolled back. But how > about independent delete queries??? > If i ran a delete statement and lost data...how do i recover. I know > that oracle has this provision of rollingback queries. > Iam surprised iam not able to find the same in postgres. > > Sam Postgres certainly can roll back queries, table creations, and many other actions. You can find more information about rollback here: http://www.postgresql.org/docs/8.3/interactive/sql-rollback.html Best of luck in your endeavor :) -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unnecessary repeat condition for a self inner join
Hi, I am not sure if this is a simple (... stupid) question but I just wasted two hours optimizing a query, so I thought I should drop in to ask. The only difference between query1 and query2 (below) is that despite an explicit INNER JOIN, I have repeated the same condition for n2 (as given for n1) and this makes a whole lot of difference in performance (since it now uses the same index for n2 that it is using for n1). In case of an INNER JOIN, shouldn't the second condition (in Query2) be unnecessary ? Or am I being unreasonable in this expectation ? Regards, *Robins Tharakan* p.s.: The query below is just a simplification, and provides only EXPLAIN, but I think an EXPLAIN ANALYSE should be unnecessary here. In case anyone still needs it, please do tell. *Query 1*: SELECT n1.scheme_code FROM nav n1 INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code WHERE n1.scheme_code BETWEEN 100 AND 200 "Merge Join (cost=903471.23..10248343.37 rows=622920912 width=4)" " Merge Cond: (n1.scheme_code = n2.scheme_code)" " -> Sort (cost=110929.32..111458.60 rows=211712 width=4)" "Sort Key: n1.scheme_code" "-> Bitmap Heap Scan on nav n1 (cost=8623.86..92201.54 rows=211712 width=4)" " Recheck Cond: ((scheme_code >= 100) AND (scheme_code <= 200))" " -> Bitmap Index Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)" "Index Cond: ((scheme_code >= 100) AND (scheme_code <= 200))" " -> Sort (cost=792541.91..805391.17 rows=5139702 width=4)" "Sort Key: n2.scheme_code" "-> Seq Scan on nav n2 (cost=0.00..131799.02 rows=5139702 width=4)" *Query 2*: SELECT n1.scheme_code FROM nav n1 INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code WHERE n1.scheme_code BETWEEN 100 AND 200 AND n2.scheme_code BETWEEN 100 AND 200 "Merge Join (cost=221858.63..607790.72 rows=25659043 width=4)" " Merge Cond: (n2.scheme_code = n1.scheme_code)" " -> Sort (cost=110929.32..111458.60 rows=211712 width=4)" "Sort Key: n2.scheme_code" "-> Bitmap Heap Scan on nav n2 (cost=8623.86..92201.54 rows=211712 width=4)" " Recheck Cond: ((scheme_code >= 100) AND (scheme_code <= 200))" " -> Bitmap Index Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)" "Index Cond: ((scheme_code >= 100) AND (scheme_code <= 200))" " -> Sort (cost=110929.32..111458.60 rows=211712 width=4)" "Sort Key: n1.scheme_code" "-> Bitmap Heap Scan on nav n1 (cost=8623.86..92201.54 rows=211712 width=4)" " Recheck Cond: ((scheme_code >= 100) AND (scheme_code <= 200))" " -> Bitmap Index Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)" "Index Cond: ((scheme_code >= 100) AND (scheme_code <= 200))"
[SQL] When was my database created
Hopefully this is an easy one and sorry if I should have found this in the docs somewhere but a cursory glance didn't turn anything up. How do I tell how old my database is, that is, when was create db for this database done? Thank you, Matthew O'Connor -- 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] Rollback in Postgres
On Fri, Jul 11, 2008 at 9:43 AM, samantha mahindrakar <[EMAIL PROTECTED]> wrote: > Hi all > This is a very basic question.can we roll back data after we run a > query. > I know that a delete within a transaction can be rolled back. But how about > independent delete queries??? > If i ran a delete statement and lost data...how do i recover. I know that > oracle has this provision of rollingback queries. > Iam surprised iam not able to find the same in postgres. If you were not in a query, then you cannot just roll back. This is because each statement is an individual transaction and a delete query "outside" a transaction is actually a begin;delete...;commit; in nature. Oracle only supports the rollback after commit if you have the right module installed and activated. And it uses up a fair bit of disk space to do it. TANSTAAFL. IF you have PITR setup in postgresql then you can recover to a previous point in time. Otherwise, you need to restore from backups. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] function that returns a set of records and integer(both of them)
Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer in the same procedure, i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql? Thanks. _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
Re: [SQL] Rollback in Postgres
On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote: > rollback after commit Are you sure? Personally I don't think its viable. If it really does that it will would also need to rollback all transactions whose changes depend upon the earlier transaction. It would also need to track transactions that read data changed by an earlier transaction and then makes changes to the database. It's got no way to track that without extensive and costly additional infrastructure, since after transaction commit row locking information can be cleaned up by read-only transactions accessing those changed data blocks. Flashback query allows reading data as it was at a certain point in the past. We might one day provide that, but undoing individual transactions isn't ever going to be feasible, without unknowable risk. Not jumping on you, just think their marketing is ahead of the reality. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] When was my database created
Matthew T. O'Connor wrote: How do I tell how old my database is, that is, when was create db for this database done? Short answer: you can't - at least not reliably and directly. You can look in the data directory associated with the database in which you are interested and check the earliest file timestamp or the timestamp of the PG_VERSION file. *IF* the files weren't touched and *if* they weren't manually copied at any time (and, of course if the clock was set correctly when the DB was created) then they *probably* represent the time the current version of the database was created. If you restored from a dump or otherwise recreated the database for any reason (version upgrade, machine migration, disaster recovery, etc.), the timestamps would represent the time of the restore, not the time of the creation of the original database. Cheers, Steve -- 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] Unnecessary repeat condition for a self inner join
"Robins Tharakan" <[EMAIL PROTECTED]> writes: > In case of an INNER JOIN, shouldn't the second condition (in Query2) be > unnecessary ? > Or am I being unreasonable in this expectation ? > SELECT n1.scheme_code > FROM nav n1 > INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code > WHERE n1.scheme_code BETWEEN 100 AND 200 > AND n2.scheme_code BETWEEN 100 AND 200 While the optimizer theoretically could deduce the extra restriction condition, it doesn't attempt to. It's extremely unclear that the extra cycles to look for such cases would be repaid on average, because cases like this aren't that common. The current state of affairs is that the system will deduce implied equality conditions, but not implied inequality conditions. [ thinks for a bit... ] The current policy has been driven in part by the assumption that looking for cases where such a deduction could apply would be pretty expensive. I wonder though whether the recent EquivalenceClass work has changed the landscape. We now store an explicit representation of the btree opclasses associated with each equivalence condition, which is one of the pieces that would be needed to match up the equivalences with inequality conditions. I'm still dubious, but that's at least one less catalog search ... 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
[SQL] PERSISTANT PREPARE (another point of view)
Hi, We are new to Postgre, actually we are migrating from MICROSOFT DBMS technologies to...hopefully Postgre. Our company is involved in ERP business software in Serbia and region, currently counting over 200 clients. Some of them have DB's over 4GB in size. Reason for posting is implementation of PREPARE statements. I've read a thread "# PREPARE and stuff PFC" on pgsql-performance 2007-06 list and I do agree that it would not gain performance issues. What could we gain by introducing a kind of global prepared statement area, is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE. Here is our point of view: We have an application layer running over db layer. Application layer consists of classes and interfaces and db layer contains data and various data manipulation structures. Application layer calls SQL statements expecting some datasets as results (inventory list for instance). What it doesn't care about is HOW is query built (sorting, conditions, etc.) as long as it returns EXPECTED columns. Application simplly calls EXECUTE (,...). Developers working application layer do not interfere with developers working on DB and queries. Plus MOST queries can be written to be reusable in various situations !!! The idea is: LETS SEPARATE SQL STATEMENTS FROM APPLICATION CODE. This way, we can introduce fine tuning to each of our clients without having to recompile our application. We can also work on improvements of queries performance and complexity without recompile of the application layer. Since one company has one set of rules PREPARED statements apply to every client connected to that database. Now, instead of preparing statements on each connection request (and we use around 900 prepared statements), why couldn't we simply prepare these statements ONCE and keep them in some global storage for future everyday usage. We use this approach for forms & reports creation where Crystal Report engine creates outlined report based on static prepared statement. This approach is probably not suitable for large db systems with tons of data, but is very efficient in 90% of small and medium business size databases. Please consider this issue when planning your WISH LIST or hopefully To-do-task-list. Ill be glad to here comments on this topic as well. Milan Oparnica MELANY SOFWARE TEAM _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us
Re: [SQL] PERSISTANT PREPARE (another point of view)
Milan Oparnica <[EMAIL PROTECTED]> writes: > [ much snipped ] > What could we gain by introducing a kind of global prepared statement area, > is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE. > The idea is: LETS SEPARATE SQL STATEMENTS FROM APPLICATION CODE. Most people around this project think that the best way to do that is to push as much logic as you can into server-side stored procedures. That gives you every advantage that a persistent-prepared-statement feature would offer, and more besides: * you can push procedural logic, as well as SQL, out of the application * you can improve performance by reducing the number of network round trips needed to accomplish a multi-SQL-statement task 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)
At 04:31 PM 7/11/2008, [EMAIL PROTECTED] wrote: Date: Fri, 11 Jul 2008 23:31:03 + From: Milan Oparnica <[EMAIL PROTECTED]> To: Subject: PERSISTANT PREPARE (another point of view) Message-ID: <[EMAIL PROTECTED]> [snip] What could we gain by introducing a kind of global prepared statement area, is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE. Here is our point of view: [snip] Now, instead of preparing statements on each connection request (and we use around 900 prepared statements), why couldn't we simply prepare these statements ONCE and keep them in some global storage for future everyday usage. Hi, What's wrong with using complex views, stored procedures, functions and maybe even custom data types to accomplish what you want here? It seems like you could build a lot of "prepared statements" using these tools, providing your application layer developers with a consistent set of interfaces to obtain data that are not tied to the data tables themselves. And allowing them to insert/update/manage tables via structured interfaces as well. Am I missing something? Best, Steve -- 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] Rollback in Postgres
In addition to allowing you to read old data, Flashback will allow you to rollback to a point in time, including returning a single table to a specific state. Flashback database is like PITR without the log files. It started in 9i and improved dramatically in 10g. 11g has made additional improvements. http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro007.htm http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmflash.htm Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Postgres Forums http://postgres.enterprisedb.com/forum.do --- On Fri, 7/11/08, Simon Riggs <[EMAIL PROTECTED]> wrote: > From: Simon Riggs <[EMAIL PROTECTED]> > Subject: Re: [SQL] Rollback in Postgres > To: "Scott Marlowe" <[EMAIL PROTECTED]> > Cc: "samantha mahindrakar" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org > Date: Friday, July 11, 2008, 2:58 PM > On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote: > > rollback after commit > > Are you sure? > > Personally I don't think its viable. If it really does > that it will > would also need to rollback all transactions whose changes > depend upon > the earlier transaction. It would also need to track > transactions that > read data changed by an earlier transaction and then makes > changes to > the database. It's got no way to track that without > extensive and costly > additional infrastructure, since after transaction commit > row locking > information can be cleaned up by read-only transactions > accessing those > changed data blocks. > > Flashback query allows reading data as it was at a certain > point in the > past. We might one day provide that, but undoing individual > transactions > isn't ever going to be feasible, without unknowable > risk. > > Not jumping on you, just think their marketing is ahead of > the reality. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > > -- > 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
