Re: [SQL] How to secure PostgreSQL Data for distribute?
> Premsun Choltanwanich wrote: > >> Dear All, >> I need to distribute my application that use PostgreSQL as >> database to my customer. But I still have some questions in my mind >> on database security. I understand that everybody who get my >> application database will be have a full control permission on my >> database in case that PostgreSQL already installed on their computer >> and they are an administrator on PostgreSQL. So that mean data, >> structure and any ideas contain in database will does not secure on >> this point. Is my understanding correct? >> What is the good way to make it all secure? Please advise. > > If your customer can access the data, they can access the data. If > they have control over the system, they can access the system. > > I guess you could build some sort of encryption into your client, but > that seems pretty easy to circumvent. > > The short answer is that there is no good way to do this. If you are > worried about this, the technology isn't going to save you. No > technology will save you. Instead, I would highly suggest discussing > the matter with an attourney and see if there is a legal remedy that > might provide adequate protection. It looks as though the Original Poster is in Thailand; if the customer is elsewhere in Asia, it might become challenging to find a context where "legal jurisdiction" or "legal remedy" are well enough defined for this to work out well. It may be that the prime issue is whether or not the customer is trustworthy or not; if the answer is "not," and legal remedies are not easy to get, then there are essentially two choices: 1. Do not distribute the database. The customer must access the database from the vendor's site. At the extreme end of this, the application would not directly submit database queries, but rather redefine the application in a client/server fashion where the customer side submits requests via some protocol that does not expose anything about the database schema. 2. If the customer is REALLY not able to be trusted, then maybe they can't be a customer. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://cbbrowne.com/info/ If we were meant to fly, we wouldn't keep losing our luggage. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [despammed] converting varchar to integer
A function to check for valid integers:
CREATE OR REPLACE FUNCTION retInt(VARCHAR) RETURNS integer AS '
DECLARE
number ALIAS FOR $1;
i INTEGER := 1;
BEGIN
IF ((number IS NULL) OR (number = )) THEN
RETURN NULL;
END IF;
WHILE (i <= length(number)) LOOP
IF ((substr(number,i,1) < ''0'') OR (substr(number,i,1)) > ''9'') THEN
RETURN NULL;
END IF;
i := i + 1;
END LOOP;
RETURN to_number(number,''990'');
END;
' LANGUAGE plpgsql;
\pset null (NULL)
DROP TABLE test;
CREATE TABLE test (number VARCHAR);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123');
INSERT INTO test VALUES('123b');
INSERT INTO test VALUES('');
SELECT retInt(number) FROM test;
retint
123
(NULL)
(NULL)
(NULL)
(4 lines)
___
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] A Table's Primary Key Listing
Hi to all, Is there any means to get a list of the Primary Keys (or simply the Primary Key if there's only one :) ) for a given table using an SQL query ? Regards, Roger Tannous. __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 1: 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] A Table's Primary Key Listing
On Thu, Aug 18, 2005 at 07:36:22AM -0700, Roger Tannous wrote: > Is there any means to get a list of the Primary Keys (or simply the > Primary Key if there's only one :) ) for a given table using an SQL query? Are you looking for the primary key definition or do you want the primary key values themselves? It's not clear what problem you're trying to solve if "SELECT columnname FROM tablename" isn't the answer. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] A Table's Primary Key Listing
RT> Hi to all, RT> Is there any means to get a list of the Primary Keys (or simply the RT> Primary Key if there's only one :) ) for a given table using an SQL query RT> ? RT> Regards, RT> Roger Tannous. Something like this? select (select attname from pg_attribute where attrelid=pg_index.indrelid and pg_attribute.attnum=pg_index.indkey[0]) from pg_index where indisprimary and indrelid=(select oid from pg_class where relname='yourtable'); DAQ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A Table's Primary Key Listing
On Thu, 18 Aug 2005 07:36:22 -0700 (PDT) Roger Tannous <[EMAIL PROTECTED]> wrote: > Is there any means to get a list of the Primary Keys (or simply the > Primary Key if there's only one :) ) for a given table using an SQL query Here is what I do in PyGreSQL: SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND pg_index.indkey[0]=pg_attribute.attnum -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 1: 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] A Table's Primary Key Listing
Thanks for your query :) But it only shows the first of the primary keys of tables having multiple primary keys :) This is apparently because of the pg_index.indkey[0] thing, so how can we manage this query in order to get all of the keys :) Thanks in advance, Roger Tannous. --- "D'Arcy J.M. Cain" wrote: > On Thu, 18 Aug 2005 07:36:22 -0700 (PDT) > Roger Tannous <[EMAIL PROTECTED]> wrote: > > Is there any means to get a list of the Primary Keys (or simply the > > Primary Key if there's only one :) ) for a given table using an SQL > query > > Here is what I do in PyGreSQL: > > SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname > FROM pg_class > JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND > pg_namespace.nspname NOT LIKE 'pg_%' > JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND > pg_attribute.attisdropped='f' > JOIN pg_index ON pg_index.indrelid=pg_class.oid AND > pg_index.indisprimary='t' AND > pg_index.indkey[0]=pg_attribute.attnum > > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] A Table's Primary Key Listing
Yes, I want only field names, not values. Thanks, Roger Tannous. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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] A Table's Primary Key Listing
On Thu, 18 Aug 2005 09:40:57 -0700 (PDT) Roger Tannous <[EMAIL PROTECTED]> wrote: > Thanks for your query :) > > But it only shows the first of the primary keys of tables having multiple > primary keys :) > > This is apparently because of the pg_index.indkey[0] thing, so how can we > manage this query in order to get all of the keys :) That's a good question. The following query does this in a very unsatisfactory way. Anyone know what the general solution would be? SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND ( pg_index.indkey[0]=pg_attribute.attnum OR pg_index.indkey[1]=pg_attribute.attnum OR pg_index.indkey[2]=pg_attribute.attnum OR pg_index.indkey[3]=pg_attribute.attnum OR pg_index.indkey[4]=pg_attribute.attnum OR pg_index.indkey[5]=pg_attribute.attnum OR pg_index.indkey[6]=pg_attribute.attnum OR pg_index.indkey[7]=pg_attribute.attnum OR pg_index.indkey[8]=pg_attribute.attnum OR pg_index.indkey[9]=pg_attribute.attnum ) ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 1: 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] A Table's Primary Key Listing
"D'Arcy J.M. Cain" writes: > That's a good question. The following query does this in a very > unsatisfactory way. Anyone know what the general solution would be? > ... > ( > pg_index.indkey[0]=pg_attribute.attnum OR > pg_index.indkey[1]=pg_attribute.attnum OR > pg_index.indkey[2]=pg_attribute.attnum OR > pg_index.indkey[3]=pg_attribute.attnum OR > pg_index.indkey[4]=pg_attribute.attnum OR > pg_index.indkey[5]=pg_attribute.attnum OR > pg_index.indkey[6]=pg_attribute.attnum OR > pg_index.indkey[7]=pg_attribute.attnum OR > pg_index.indkey[8]=pg_attribute.attnum OR > pg_index.indkey[9]=pg_attribute.attnum > ) In CVS tip you could replace this with "attnum = ANY (indkey)". Unfortunately, most array support doesn't work on int2vector in pre-8.1 releases, so I think you're kinda stuck with the above for now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] dates and selection
After my conversion to Unicode and implementing new drivers (Thank god still up no down time J) I do have an issue with some of my sql selects concerning dates. I know the long answer, but am asking if there is a short one. I have in some of my slq : (to_char(e.incidentdate, 'Mon DD '::text) || ' '::text) || e.incidenttime::text as incidentdate I used to be able to sort and select by incident date and it was working ok (I think). Now I found I had to do something like this just to have a timestamp (problem is I do not want the format of the time stamp, my clients want to see the month as a string) ((to_char(e.incidentdate, 'Mon DD '::text) || ' '::text) || e.incidenttime::text)::timestamp as datetoselectby Is there any way to reference the text type variable as a date selection? (was this ever working or was I hallucinating). Many thanks for all the help. Joel Fradkin
[SQL] nevermind answered my own question by looking at my question what a DOH!
select * from viwEmpIncCube where clientnum ='MSI' and Incidentdate::timestamp between '01/01/2005' and '08/18/2005 23:59' woks fine. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] A Table's Primary Key Listing
Hi,
If you put pg_index.indkey in the select statement, you'd notice that it's
sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for two
PK fields), etc.
So I tried to use a replace command like the following:
(just to add parentheses, replace the space by a comma to use the
resulting string in an IN statement)
select '(' || replace('1 2', " ", ",") || ')';
which yields: (1,2)
But the following query fails to execute!!
select replace(indkey, " ", ",") from pg_index;
[
sub question: Did I miss quotes around elements? I mean should I enclose
every element originating from the indkey array with single quotes ? if
yes, so easy, no need to matter about it: so I should have tried the
following (which I didn't have time to do yet):
select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
Another issue here too: Could double quotes here be the source of a
problem ? So I should have tested also this query:
select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
I expect this query to work :) Let's hope so!!
]
So we can use the following WHERE statement:
WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
which should translate into: WHERE pg_attribute.attnum IN (1,2)
Finally, this WHERE statement:
WHERE pg_attribute.attnum IN
'(\'' || replace(pg_index.indkey, " ", "','") || '\')'
[
Again, I should test:
WHERE pg_attribute.attnum IN
'(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
]
I wish I had database access in the internet cafe I'm sending this message
from :) instead of just loading you with this bunch of questions.
Best Regards,
Roger Tannous.
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> "D'Arcy J.M. Cain" writes:
> > That's a good question. The following query does this in a very
> > unsatisfactory way. Anyone know what the general solution would be?
>
> > ...
> > (
> > pg_index.indkey[0]=pg_attribute.attnum OR
> > pg_index.indkey[1]=pg_attribute.attnum OR
> > pg_index.indkey[2]=pg_attribute.attnum OR
> > pg_index.indkey[3]=pg_attribute.attnum OR
> > pg_index.indkey[4]=pg_attribute.attnum OR
> > pg_index.indkey[5]=pg_attribute.attnum OR
> > pg_index.indkey[6]=pg_attribute.attnum OR
> > pg_index.indkey[7]=pg_attribute.attnum OR
> > pg_index.indkey[8]=pg_attribute.attnum OR
> > pg_index.indkey[9]=pg_attribute.attnum
> > )
>
> In CVS tip you could replace this with "attnum = ANY (indkey)".
> Unfortunately, most array support doesn't work on int2vector in
> pre-8.1 releases, so I think you're kinda stuck with the above
> for now.
>
> regards, tom lane
>
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] A Table's Primary Key Listing
Hi to all, there was a BIG MISTAKE in my proposition regarding my last
post:
In fact, after examining the online documentation (Note that I don't have
enough experience in postgreSQL !!) I found that
select '(' || replace('1 2', " ", ",") || ')';
could not, in any way, be equivalent to:
select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
in that the first example '1 2' is a string, while indkey is an array and
the later usage of the concatenation operator with the array just appends
strings to the array, which yields an array, not what I expected to be, a
string!! So it's apparently irrelevant to directly use the replace command
with an array !!
In fact, I've also tried:
select replace('(\'' || indkey || '\')', " ", "','") from pg_index;
but forgot to mention it in the previous post.
So concatenating any string to an array yields an array... and this query
is irrelevant.
The possible solution would be to convert this array to a string, with the
insertion of the proper quotes and commas; but since the command to be
used already inserts a delimiter, we can get rid of the replace command.
Let's see this query now:
select '(\'' || array_to_string(indkey, '\',\'') || '\')' from pg_index;
I'm sure this should work :)
Now we have the final WHERE statement like this:
WHERE pg_attribute.attnum IN '(\'' || array_to_string(pg_index.indkey,
'\',\'') || '\')'
or ?
WHERE pg_attribute.attnum IN ('\'' || array_to_string(pg_index.indkey,
'\',\'') || '\'')
Anyway, I got to test those queries, and I'm optimistic about it.
Hope they'll work fine :)
Best Regards,
Roger Tannous.
--
--
--
--
--- Roger Tannous <[EMAIL PROTECTED]> wrote:
> Hi,
>
> If you put pg_index.indkey in the select statement, you'd notice that
> it's
> sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for
> two
> PK fields), etc.
>
> So I tried to use a replace command like the following:
>
> (just to add parentheses, replace the space by a comma to use the
> resulting string in an IN statement)
>
> select '(' || replace('1 2', " ", ",") || ')';
>
> which yields: (1,2)
>
> But the following query fails to execute!!
> select replace(indkey, " ", ",") from pg_index;
>
> [
> sub question: Did I miss quotes around elements? I mean should I enclose
> every element originating from the indkey array with single quotes ? if
> yes, so easy, no need to matter about it: so I should have tried the
> following (which I didn't have time to do yet):
>
> select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
>
> Another issue here too: Could double quotes here be the source of a
> problem ? So I should have tested also this query:
>
> select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
>
> I expect this query to work :) Let's hope so!!
> ]
>
>
>
> So we can use the following WHERE statement:
> WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
>
> which should translate into: WHERE pg_attribute.attnum IN (1,2)
>
>
> Finally, this WHERE statement:
>
> WHERE pg_attribute.attnum IN
> '(\'' || replace(pg_index.indkey, " ", "','") || '\')'
>
>
> [
> Again, I should test:
>
> WHERE pg_attribute.attnum IN
> '(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
>
> ]
>
>
> I wish I had database access in the internet cafe I'm sending this
> message
> from :) instead of just loading you with this bunch of questions.
>
>
> Best Regards,
> Roger Tannous.
>
>
> --- Tom Lane <[EMAIL PROTECTED]> wrote:
>
> > "D'Arcy J.M. Cain" writes:
> > > That's a good question. The following query does this in a very
> > > unsatisfactory way. Anyone know what the general solution would be?
> >
> > > ...
> > > (
> > > pg_index.indkey[0]=pg_attribute.attnum OR
> > > pg_index.indkey[1]=pg_attribute.attnum OR
> > > pg_index.indkey[2]=pg_attribute.attnum OR
> > > pg_index.indkey[3]=pg_attribute.attnum OR
> > > pg_index.indkey[4]=pg_attribute.attnum OR
> > > pg_index.indkey[5]=pg_attribute.attnum OR
> > > pg_index.indkey[6]=pg_attribute.attnum OR
> > > pg_index.indkey[7]=pg_attribute.attnum OR
> > > pg_index.indkey[8]=pg_attribute.attnum OR
> > > pg_index.indkey[9]=pg_attribute.attnum
> > > )
> >
> > In CVS tip you could replace this with "attnum = ANY (indkey)".
> > Unfortunately, most array support doesn't work on int2vector in
> > pre-8.1 releases, so I think you're kinda stuck with the above
> > for now.
> >
> > regards, tom lane
> >
>
>
>
>
>
> Start your day with Yahoo! - make it you
Re: [SQL] How to secure PostgreSQL Data for distribute?
On 8/17/05, Premsun Choltanwanich <[EMAIL PROTECTED]> wrote: Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise. If it is "your" database, then I would not give them the database, but merely offer the information in the database as a "service." This, of course, can be implemented through the internet. That way, the rules that govern which customers can access and see which pieces of data can be implemented in the application itself so you wouldn't have give them all the data and structure the customer doesn't need to see if the rules governing how they access the database from the application are built into the application itself, with possibly authentication credentials stored in the database and the athentication mechanism implemented in the application. The only other way I can imagine where you can get beyond, atleast not showing the data they don't need to see, is querying the database to filter out the data 'belonging' to the customer, and export that filtered data to isolated tables comprising a new database that could then be given to the customer, watered down to what's relevant to them. If it's "your" database then, hey, don't give it to them. If it's really "their" database then you wouldn't really have a problem with giving them their data... But it's apparently not really "their" database so keep it to yourself and offer access to the data as a service. Ferindo -- Ferindo Middleton Chief Architect Sleekcollar.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SQL CASE Statements
In the following CASE statement, is it possible to put a SELECT ... WHERE
EXISTS
in the of a CASE statement, and have it work?
The I want to do is to yield a result of '1' if the statement
finds
the value 'a' in a table (EXISTS evaluates true), and '0' if it evaluates
false
('a' not found).
SELECT a,
CASE WHEN THEN 1
ELSE 0
END
Has anybody done this? If so, can you send me a sample?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SQL CASE Statements
I am not sure what you are asking...
SELECT CASE WHEN EXISTS (SELECT foo FROM bar WHERE baz = 'a') THEN 1
ELSE 0 END;
Or
SELECT CASE WHEN 'a' = ANY (SELECT froo FROM bar) THEN 1 ELSE 0 END;
Both work, but that's pretty much what you had already - am I missing
what you are trying to achieve?
Though both are likely to be quite inefficient if you are looking up
many values.
Maybe something like:
SELECT f.a, CASE WHEN b.a IS NOT NULL THEN 1 ELSE 0 END
FROM foo f LEFT JOIN bar b USING (a)
Assuming "foo" has the values you want to look up, and "bar" is the
table you check for existence.
Dmitri
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen
> Sent: Thursday, August 18, 2005 9:32 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SQL CASE Statements
>
>
> In the following CASE statement, is it possible to put a
> SELECT ... WHERE EXISTS in the of a CASE
> statement, and have it work?
>
> The I want to do is to yield a result of '1' if
> the statement finds the value 'a' in a table (EXISTS
> evaluates true), and '0' if it evaluates false ('a' not found).
>
> SELECT a,
> CASE WHEN THEN 1
>ELSE 0
> END
>
> Has anybody done this? If so, can you send me a sample?
>
>
>
> ---(end of
> broadcast)---
> TIP 4: Have you searched our list archives?
>
http://archives.postgresql.org
The information transmitted is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please contact
the sender and delete the material from any computer
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] dates and selection
Joel, > Now I found I had to do something like this just to have a timestamp > (problem is I do not want the format of the time stamp, my clients want to > see the month as a string) Um, what's wrong with: to_char(some_timestamp, 'Mon DD HH:MI:SS') ? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Updateing pg_trigger and pg_constraint
I am trying to make some foreign keys deferrable and initially deferred. These foreign key constraints already exist so I was going to change them by updating pg_trigger and pg_constraint. However the changes do not seem to take affect. Is there something I need to do to get PostgreSQL to recognize that I have tweaked it's tables? This is the query that I used: begin work; update pg_constraint set condeferrable = true, condeferred = true where contype ='f'; update pg_trigger set tgdeferrable = true, tginitdeferred = true where tgconstrname in ( select conname from pg_constraint where contype = 'f' ); commit any help would be appreciated, Craig ---(end of broadcast)--- TIP 1: 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] Parentheses in FROM clause and evaluation order.
Ok, thanks for the responses guys. Then, in the case where the final result is the same, could we think the parentheses in the FROM clause, as a tool to clarify the query to the user? Since in the end, this order could be changed by the implementation for performance reasons. salu2 dario estepario ... 2005/8/15, Tom Lane <[EMAIL PROTECTED]>: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 15 Aug 2005, Dario Bahena Tapia wrote: > >> The final result seems to be the same, I just was curious about the > >> standard behavior. Does the SQl says something about this execution > >> order? > > > I believe SQL defines the order to pay attention to parens, so A join (B > > join C) style clauses result in a "table" being derived from B join C and > > another from A joined with that table. > > SQL only constrains the results, though. It does not forbid the > implementation from doing the work in whatever way seems best to it, > so long as the results are the same (and "same" does not consider > row ordering). > > For example, SQL92 3.3.4.4 says > > A conforming implementation is not required to perform the exact > sequence of actions defined in the General Rules, but shall achieve > the same effect on SQL-data and schemas as that sequence. > > > regards, tom lane > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] pgsql-bugs
From: "Lee Hyun soon" <[EMAIL PROTECTED]> To: pgsql-bugs@postgresql.org Date: Wed, 17 Aug 2005 05:36:23 +0100 (BST) Subject: BUG #1826: pgsql odbc & ADO.NET The following bug has been logged online: Bug reference: 1826 Logged by: Lee Hyun soon Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.2 Operating system: Windows XP Professional SP1 Description:pgsql odbc & ADO.NET Details: I'm a Corean Coder. During C# Coding, I Found it. - http://www.windows.or.kr/zboard/bbs/view.php?id=app1data&page=1&sn1=&divpage =1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=6 --- Download #1 : RichEditBoxTest.zip (18.6 KB) Download #2 : input_data.txt (3.2 KB) RichEditBoxTest.zip is Test Program(C#.net) input_data.txt is input Data i use latest odbc provider, and it's database scheme is == CREATE TABLE nmsdata ( orgin_code char(17) NOT NULL DEFAULT to_char(now(), 'mmddHH24MISSMS'::text), data text, datetime timestamp DEFAULT now(), bigo1 varchar(50), "year" char(4), data_gubun numeric DEFAULT 0 ) WITHOUT OIDS; ALTER TABLE nmsdata OWNER TO postgres; GRANT ALL ON TABLE nmsdata TO postgres; GRANT ALL ON TABLE nmsdata TO public; == and, odbc dsn is "remote" the problem is short string is "insert" DML processing completely. and long string is also. but, after shot string "select" DML, data's tail is broken. u see my source(if u know C# Language and have .Net Comfiler), u catch this problem. - i cannot use english T^T well. i hope that u catch my problem. and this problem will be solved. - Corean(Korean) Lee. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] PGSQL function for converting between arbitrary numeric bases?
Hello, I'm looking - without luck so far - for a PGSQL function for converting numbers between two arbitrary bases (typically base 10,16 and 26 in my case). Something similar to the C 'strtol' function or, ideally, PHP's baseconvert(string,frombase,tobase) function. I've search the docs, lists and various other forums but so far no luck. Just wondered if I've missed anything - all suggestions gratefully received. Many thank, Simon K ---(end of broadcast)--- TIP 1: 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] sql function: using set as argument
Thanks! Thomas Actually I am going to use many functions for different counts and a wrapper function to return all count in one shot. My function1 is going to be a part of all count functions. If I use it as sub query in all the functions, performance degrades drastically, as my query of finction1 is also heavy. My wrapper function is going to look like: Get_all_counts() Select count1(result_set_of_finction1, int, int), count2(result_set_of_finction1, int, int), count3(result_set_of_finction1, int, int)……. Regards, akshay --- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. (www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445 -Original Message- From: Thomas F. O'Connell [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 3:39 AM To: Akshay Mathur Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] sql function: using set as argument Is there a reason not to build it in as a sub-query? E.g., if you have a function get_count( int ): SELECT count(b_column) FROM some_table WHERE some_field_1 in ( SELECT a_column FROM a_table WHERE some_condition ) AND some_field_2 = $2; -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 10, 2005, at 9:30 AM, Akshay Mathur wrote: Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay --- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. (www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445
[SQL] Tables are not being updated Properly through Trigger
Title: Tables are not being updated Properly through Trigger We have written a trigger on insertion which is supposed to update 3 tables with new data. We are seeing a weird thing happening with Postgres, i.e. If we have 4 records to be updated on insertion trigger, postgres updates the first 3 records in the 3 tables properly, and when it comes to final record, it is updating only the first 2 tables in sequence and is skipping the 3rd table. So, we are ending up with the improper data. Any body has come across such an issue earlier or can anybody tell us whats happening here ? Thanks, Venkatesh
Re: [SQL] Parentheses in FROM clause and evaluation order.
Hi, The final result seems to be the same, I just was curious about the standard behavior. Does the SQl says something about this execution order? Thanks for your response. salu2 dario estepario ... 2005/8/15, Stephan Szabo <[EMAIL PROTECTED]>: > On Wed, 10 Aug 2005 [EMAIL PROTECTED] wrote: > > > I thought that the parenthesis in the table expression > > (FROM clause), could be used to indicate the desired > > evaluation order. But, I tried with a couple of samples > > and the explain command returned me the same result; no matter > > what parentheses association I used. I am using only INNER JOINs. > > > > In fact, I thought that the whole table expression was gonna be > > evaluated before the WHERE filter. Does the stantard says something > > about this evaluation order when the parentheses are present? > > Does PostgreSQL implements this behavior? > > AFAIK we only try to provide final results that are equivalent to > following the steps in order, so it'll reorder joins or push clauses > around as long as it thinks the semantics of the query won't change. For > example, actually doing unconstrainted joins before where clauses is a > very bad plan if you've got a FROM table1, table2, table3 style query. If > you're seeing a place where the reorder affects the query results as > opposed to the query plan, that's probably a bug, can you give more > information? > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Tables are not being updated Properly through Trigger
am 15.08.2005, um 18:07:23 +0530 mailte Venkatesh Krishnamurthy folgendes: > We have written a trigger on insertion which is supposed to update 3 > tables with new data. We are seeing a weird thing happening with > Postgres, i.e. If we have 4 records to be updated on insertion trigger, > postgres updates the first 3 records in the 3 tables properly, and when > it comes to final record, it is updating only the first 2 tables in > sequence and is skipping the 3rd table. So, we are ending up with the > improper data. Any body has come across such an issue earlier or can > anybody tell us whats happening here ? Nobody can guess your problem without the source-code of your trigger an a simple example. Can you poste this on http://rafb.net/paste/ and tell us then the link? 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 5: don't forget to increase your free space map settings
[SQL] pl/PgSQL: Samples doing UPDATEs ...
I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it, but no good samples What I'm looking for is a sample of a function that returns # of rows updated, so that I can make a decision based on that ... does anyone know where I could find such (and others, would be great) online? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pl/PgSQL: Samples doing UPDATEs ...
MGF> I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it, MGF> but no good samples MGF> What I'm looking for is a sample of a function that returns # of rows MGF> updated, so that I can make a decision based on that ... does anyone know MGF> where I could find such (and others, would be great) online? ... execute ''Update ...''; GET DIAGNOSTICS processed_rows = ROW_COUNT; return processed_roows; ... See PostgreSQL 7.3 online documentation 19.5.5. Obtaining result status. http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html DAQ ---(end of broadcast)--- TIP 1: 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] pl/PgSQL: Samples doing UPDATEs ...
On Fri, Aug 19, 2005 at 02:38:01AM -0300, Marc G. Fournier wrote:
> I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it,
> but no good samples
>
> What I'm looking for is a sample of a function that returns # of rows
> updated, so that I can make a decision based on that ... does anyone know
> where I could find such (and others, would be great) online?
Are you looking for GET DIAGNOSTICS?
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
CREATE TABLE foo (id integer, name text);
INSERT INTO foo VALUES (1, 'Bob');
INSERT INTO foo VALUES (2, 'Bob');
INSERT INTO foo VALUES (3, 'Jim');
CREATE FUNCTION update_foo(old_name text, new_name text) RETURNS integer AS $$
DECLARE
num_rows integer;
BEGIN
UPDATE foo SET name = new_name WHERE name = old_name;
GET DIAGNOSTICS num_rows = ROW_COUNT;
RETURN num_rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;
SELECT update_foo('Bob', 'Robert');
update_foo
2
(1 row)
SELECT update_foo('Jim', 'James');
update_foo
1
(1 row)
SELECT update_foo('Rick', 'Richard');
update_foo
0
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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
