Re: [SQL] Clarification With Money data type
On 2010-03-10, Navanethan Muthusamy wrote: > --0016e68e9a5510f1f504816d1fcb > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, > > I am using Postgresql 8.4, Can you tell me How Can I use Money data type? it's best not to, "money" is a fixed-point fromat based on 32 bit integers, abn it's deprecated. > I want to store the money and retrieve. Please give me idea to work on that. for that you use a bank, not a database :) > I am using Java with Postgresql, I have tried java.math.BigDecimal with > Money, but its giving error. (Its asking me to do the casing) ??? it's better to use some sort of numeric -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Quoth dennis : > Dear Ben > > thanks for you anwser. > I try to add function quote_literal on my sql statement . > > but it raise other error message (quote_literal not support bytea format): > function quote_literal(bytea) does not exist Which Postgres version are you using? Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Selecting names of indexes that are not dependent on constraints.
Hey all, How can I select names of indexes of table (or even whole schema) that are not dependent on constraints? Unfortunately there is no way to select only "independent" indexes from pg_indexes view. How can I do it? Regards, Dmitriy Igrishin
Re: [SQL] Selecting names of indexes that are not dependent on constraints.
Of course, I meant the indexes of which constraints do not depend. :) 2010/3/11 Dmitriy Igrishin > Hey all, > > How can I select names of indexes of table (or even whole schema) that are > not dependent on constraints? > > Unfortunately there is no way to select only "independent" indexes from > pg_indexes view. > > How can I do it? > > Regards, > Dmitriy Igrishin >
Re: [SQL] Selecting names of indexes that are not dependent on constraints.
Dmitriy Igrishin writes: > How can I select names of indexes of table (or even whole schema) that are > not dependent on constraints? Right at the moment, the only reliable way to tell whether an index is associated with a constraint is to look for a pg_depend entry linking the two. Be careful to consider only "internal" dependencies, else you may be fooled by foreign-key constraints that depend on indexes. If you look into the pg_dump source code you will find an example. 9.0 will make this a tad simpler by keeping a "conindid" column in pg_constraint. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Trigger on select :-(
What I want is to have a trigger on select, but since I have searched the archives I know that is not possible. I also read that view rules is the technology is whats available. Having never used views before I can't seem to get my mind to figure out what I need to do to solve my problem. I have a table that defines a key that I will use in another table in a list of items defined in the previous table. The tables are CREATE TABLE Foo ( INTEGER key not null primary key, ... characteristics for the Foo item ); CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY, ... other catalog data ); The catalog will have rows that reference the elements in the Foo table. I also have another table that references the Foo table CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY, ... other ToDo information. What I want to do is when I do a SELECT on the Catalog and deliver the result to the user, I want to check to see if the FooKey is in the users ToDo table and set the value of a column isToDo to true or false depending on if the FooKey exists in the ToDo table for the user. If I were building a table I would use a trigger and select on the row in the ToDo table. Please, all I want is an idea where to start, what I should read and I will figure out how to do it. Thanks in advance. <> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger on select :-(
Are you able to set up a SELECT query to get the result you want? If you are, it's as simple as putting "CREATE VIEW viewname AS ", followed by your query. Garrett Murphy _ From: [email protected] [mailto:[email protected]] On Behalf Of Ray Madigan Sent: Monday, March 01, 2010 7:11 PM To: [email protected] Subject: [SQL] Trigger on select :-( What I want is to have a trigger on select, but since I have searched the archives I know that is not possible. I also read that view rules is the technology is whats available. Having never used views before I can't seem to get my mind to figure out what I need to do to solve my problem. I have a table that defines a key that I will use in another table in a list of items defined in the previous table. The tables are CREATE TABLE Foo ( INTEGER key not null primary key, ... characteristics for the Foo item ); CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY, ... other catalog data ); The catalog will have rows that reference the elements in the Foo table. I also have another table that references the Foo table CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY, ... other ToDo information. What I want to do is when I do a SELECT on the Catalog and deliver the result to the user, I want to check to see if the FooKey is in the users ToDo table and set the value of a column isToDo to true or false depending on if the FooKey exists in the ToDo table for the user. If I were building a table I would use a trigger and select on the row in the ToDo table. Please, all I want is an idea where to start, what I should read and I will figure out how to do it. Thanks in advance. << File: ATT4311246.txt >>
Re: [SQL] Help : insert a bytea data into new table
[quoting fixed] Quoth dennis : > Ben Morrow wrote: > > Quoth dennis: > >> Dear Ben > >> > >> thanks for you anwser. > >> I try to add function quote_literal on my sql statement . > >> > >> but it raise other error message (quote_literal not support bytea format): > >>function quote_literal(bytea) does not exist > > > > Which Postgres version are you using? > > Postgres : 8.1.4 Then I think you want create function quote_literal (bytea) returns text immutable strict language plpgsql as $$ begin return 'E''' || replace(encode($1, 'escape'), E'\\', E'') || ; end; $$; Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Remote monitoring of Postgres w/minimal grants
On Wed, Mar 10, 2010 at 12:26 AM, Bryce Nesbitt wrote: > I'm setting up remote monitoring of postgres, but running into an > uncomfortable situation with permissions. > Basically it seems hard to set up a secure "read only" role, yet also allow > proper monitoring. > > A brief writeup of that is here: > > http://help.logicmonitor.com/installation-getting-started/notes-for-monitoring-specific-types-of-hosts/databases/postgresql/postgresql-credentials/ > In order to get accurate server busy stats and max query time, the > LogicMonitor user needs to be a superuser "alter role logicmonitor > superuser;". Without the SuperUser privilege, all servers will appear busy, > and maximum query time will always be 0. > > Is there a way to grant the type of permission needed to view stats, > without superuser? > Seems like you could get around most of these cases by making a function or set returning function to return the data and making it "security definer" and then grant your monitoring user access to that. Tony
Re: [SQL] Trigger on select :-(
On Mon, Mar 1, 2010 at 9:10 PM, Ray Madigan wrote: > > What I want to do is when I do a SELECT on the Catalog and deliver the > result to the user, I want to check to see if the FooKey is in the users > ToDo table and set the value of a column isToDo to true or false depending > on if the FooKey exists in the ToDo table for the user. you should do this when inserting data (with triggers of course) you can't use rules because a RULE ON SELECT you only can use one SELECT, no INSERT, UPDATE nor DELETE -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
