Re: [GENERAL] Help to realise function
On Mon, 3 Apr 2006 14:57:14 +1000, "Harvey, Allan AC" <[EMAIL PROTECTED]> wrote: > Hi all, > > Can anyone offer suggestions on how to realise this function > > It is the $1 as the table name that is the problem. > > I'm using Version 7.4.5 > > create or replace function last_scan( varchar, varchar ) returns float as ' > declare > result float; > > begin > result := value from $1 where parameter = $2 order by dt desc limit 1; > > return result; > end; > ' LANGUAGE plpgsql; try executing the query declare r record; begin for r in execute 'select value from '||quote_ident($1)||' where parameter = '||quote_literal($2)||' order by dt desc limit 1' loop return r.value; end loop; return null; end; klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help to realise function
Hi all, Can anyone offer suggestions on how to realise this function It is the $1 as the table name that is the problem. I'm using Version 7.4.5 create or replace function last_scan( varchar, varchar ) returns float as ' declare result float; begin result := value from $1 where parameter = $2 order by dt desc limit 1; return result; end; ' LANGUAGE plpgsql; Thanks Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] help me...
Hi, Mr. PG.. what is PG version operating OLAP operations ? Thanks.. --Hira Sirojudin-- --Jurusan Teknik Komputer dan Informatika - Politeknik Negeri Bandung ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.1.3, libpq, PQprepare, plpgsql function, and partitioned tables
* Tom Lane ([EMAIL PROTECTED]) wrote: > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > > Am I correct in assuming that when Postgres prepared the SQL to execute > > the "insert function" that the existing rules on the base table were > > also resolved at that time? If so, is there any way to avoid that > > behavior? > > Yes; no. We are working on infrastructure to automatically redo > prepared plans when relevant catalog entries change, but it's not there > today :-( Wouldn't it be possible to use 'execute' instead and have the plan re-generated each time that way? It'd be less efficient but I think it'd work as a work-around... Just some thoughts, Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Database security granularity
Michael Trausch <[EMAIL PROTECTED]> writes: > I'd like to know if I can constrict database and data access > on a row-level with PgSQL by using some sort of trickery in the database > configuration itself. You could do this with views, on the order of create view secure_view as select * from base_table where access_allowed(current_user, ...); where you need to write an access_allowed function that implements your security policy (probably based on fields in the base table that are not reflected in the view, so it's not really gonna be "select *"). Then you grant access to the view but not the base table to the users. Also, take a look at http://pgfoundry.org/projects/veil/ and search the archives for past discussions of row-level security. regards, tom lane ---(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: [GENERAL] 8.1.3, libpq, PQprepare, plpgsql function, and partitioned tables
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > Am I correct in assuming that when Postgres prepared the SQL to execute > the "insert function" that the existing rules on the base table were > also resolved at that time? If so, is there any way to avoid that > behavior? Yes; no. We are working on infrastructure to automatically redo prepared plans when relevant catalog entries change, but it's not there today :-( regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cant find temp tables
[EMAIL PROTECTED] wrote: hi all, i am struggling with temp tables. i have a statement like CREATE TEMP TABLE test (a BIGINT); it executes successfully, hence i know that the table has been created. but i cant see the table within the DB using PGADMIN. if i do SELECT * FROM test; it returns 0 records, but it still tells me that the table exists with no data inside it. if i do a DROP TABLE test; it deletes the table too. so everything is pointing to the fact that the table is created, contains no data, but i cant see it. there is only one schema (public) too. can somebody please help me out. Sounds like a bug in pgadmin. Might be better asking that list though: http://www.pgadmin.org/support/ -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] installation problem - semaphores
u have to "su postgres" first,just like this: -- # rehash # su postgres postgres$ initdb -D /usr/local1/pgsql/data good luck! "Yadu" <[EMAIL PROTECTED]> news:[EMAIL PROTECTED] Hi, I have installed postgre-sql in directory /usr/local1/postgres Now, I am trying to run /usr/local1/postgres/bin/initdb -D /usr/local1/postgres/data I got following error. > initdb -D /usr/local1/postgres/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/local1/postgres/data ... ok creating directory /usr/local1/postgres/data/global ... ok creating directory /usr/local1/postgres/data/pg_xlog ... ok creating directory /usr/local1/postgres/data/pg_xlog/archive_status ... ok creating directory /usr/local1/postgres/data/pg_clog ... ok creating directory /usr/local1/postgres/data/pg_subtrans ... ok creating directory /usr/local1/postgres/data/base ... ok creating directory /usr/local1/postgres/data/base/1 ... ok creating directory /usr/local1/postgres/data/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in /usr/local1/postgres/data/base/1 ... FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(1, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about configuring your system for PostgreSQL. child process exited with exit code 1 initdb: removing contents of data directory "/usr/local1/postgres/data" Since I am using Solaris OS, I may need to reboot the machine to change the value of semmns. I don't want to do that. How can I reduce the parameter max_connections and rerun the command? Thank you, Yadnyesh. -- God is in details; specialization is god. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] about partitioning
hi chris
as u know i'm a chinese and a freshman to postgres so forgive my poor
english.
child table "news_001" dose inherit the table "news".
for that reason it gets all fields which the master has,and i changed
nothing on it.
when i executed a query like
"INSERT INTO news(title,content,author) VALUES('just a test','test
too','fufay')"
it stored data both "news" and "news_001" that falls short of my
expectations.
since i did a rule to redirect the "INSERT" to the child and a trigger on
"news"
that "RETURN NULL" when get "INSERT" action,but they didn't work any way.
if we can't restrict to insert data into parent,we'll get two copies at
all --in parent and in child.
best regards
fufay
""chris smith"" <[EMAIL PROTECTED]> дÈëÏûÏ¢
news:[EMAIL PROTECTED]
> On 4/1/06, fufay <[EMAIL PROTECTED]> wrote:
>> dear all,
>> i created a master table and a sub table which inherits the main table.
>> and then i made a trigger and a function that want to keep the master
>> table
>> empty.
>> but the trigger didn't work anyway.when i inserted data into the table
>> "news",both the master table
>> and the sub table were inserted.
>> why? i just want the empty master table,any good ideas?
>> lots of thanks for all.
>>
>> here r DDls:
>> -
>> --master table£»
>> CREATE TABLE "public"."news" (
>> "id" SERIAL,
>> "title" VARCHAR(100) NOT NULL,
>> "content" VARCHAR NOT NULL,
>> "author" VARCHAR(50) NOT NULL,
>> "date" DATE DEFAULT now(),
>> CONSTRAINT "news_pkey" PRIMARY KEY("id")
>> )WITHOUT OIDS;
>>
>> --rule£»
>> CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
>> DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES
>> (new.title,
>> new.content, new.author));
>>
>> --trigger£»
>> CREATE TRIGGER "news_triggers" BEFORE INSERT
>> ON "public"."news" FOR EACH ROW
>> EXECUTE PROCEDURE "public"."deny_insert"();
>>
>> --function£»
>> CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
>> $body$
>> BEGIN
>> RETURN NULL;
>> END;
>> $body$
>> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>>
>> --sub table£»
>> CREATE TABLE "public"."news_001" (
>> CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
>> (date < '2006-04-28'::date))
>> ) INHERITS ("public"."news")
>> WITHOUT OIDS;
>
> Since the fields don't exist in news_001, it has to store them
> somewhere - in the table it inherits from.
>
> Inheritence is meant to be used to change something in the
> substructure/child table/whatever.
>
> If that object isn't in the child, it has to go back to the parent to
> work out what to do (in your case, store the entry).
>
> --
> Postgresql & php tutorials
> http://www.designmagick.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
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Database security granularity
Hello everyone, I'm working with an application, and I'm realizing that perhaps the model for security that I have used in the past won't work all that well with the application that I'm working on. I am certain that this particular model is how web applications traditionally work, but I am wondering if I can do something a little more then this in PostgreSQL. Traditionally, I have a table that contains the user names and hashed passwords for each of the users that are defined to the application. When they login, it checks against this table, and so forth. However, the application that I'm currently working on is something that I want to have a portable set of front-ends to work with. For example, I'd like a front-end in PHP for web-based access, but also I am working on creating a front-end that will be written in a more client/server fashion. I'd like to know if I can constrict database and data access on a row-level with PgSQL by using some sort of trickery in the database configuration itself. If not, that's okay, I suppose -- it'll just mean that I'll need to come up with my own client/server portions of the program, with the server side of it regulating the access. That will make it significantly more of a challenge, but it is the only other thing that I can come up with. Ideas? Thanks, Mike ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.1.3, libpq, PQprepare, plpgsql function, and partitioned tables
I have some long-running processes which connect to Postgres, use PQprepare on a function call, and use PQexecPrepared to effectively insert rows into a set of partitioned tables (by month). In a nutshell, up until yesterday I had a base widgets table and a widgets_200603 table, an INSERT rule in front of widgets to insert into 200603 based on a timestamp column, and an INSERT trigger on the base table to reject inserts into it (nothing special, just following section 5.9.2 in the 8.1.3 docs). The processes in question were started in early Mar 2006, everything worked fine. Yesterday (31 Mar) I created a widgets_200604 table and a rule & used psql to verify that the "insert function" indeed placed rows in the new table when appropriate (based on the timestamp value). However, I just noticed that the inserts from the existing processes were rejected as if the new rule was not there. Bouncing the processes fixed things, I assume because the call to the "insert function" was prepared anew. Am I correct in assuming that when Postgres prepared the SQL to execute the "insert function" that the existing rules on the base table were also resolved at that time? If so, is there any way to avoid that behavior? In case it isn't clear, by the way, the "insert function" mentioned above is roughly: CREATE OR REPLACE FUNCTION insert_widget(...) RETURNS void AS $$ BEGIN INSERT INTO widgets(...) VALUES(...) ; RETURN ; END ; $$ LANGUAGE plpgsql ; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] MediaWiki and Postgresql?
The propaganda at MediaWiki is sorta vague on Postgresql support. Has anyone put MediaWiki up using the current version of Postgresql? Jerry ---(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
[GENERAL] Cant find temp tables
hi all, i am struggling with temp tables. i have a statement like CREATE TEMP TABLE test (a BIGINT); it executes successfully, hence i know that the table has been created. but i cant see the table within the DB using PGADMIN. if i do SELECT * FROM test; it returns 0 records, but it still tells me that the table exists with no data inside it. if i do a DROP TABLE test; it deletes the table too. so everything is pointing to the fact that the table is created, contains no data, but i cant see it. there is only one schema (public) too. can somebody please help me out. thanks. ---(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: [GENERAL] installation problem - semaphores
On April 2, 2006 10:35, Tom Lane wrote: > Yadu <[EMAIL PROTECTED]> writes: > > selecting default max_connections ... 10 > > selecting default shared_buffers ... 50 > > creating configuration files ... ok > > creating template1 database in /usr/local1/postgres/data/base/1 ... > > FATAL: could not create semaphores: No space left on device > > DETAIL: Failed system call was semget(1, 17, 03600). > > HINT: This error does *not* mean that you have run out of disk space. > > It occurs when either the system limit for the maximum number of > > semaphore sets (SEMMNI), or the system wide maximum number of semaphores > > (SEMMNS), would be exceeded. You need to raise the respective kernel > > parameter. Alternatively, reduce PostgreSQL's consumption of semaphores > > by reducing its max_connections parameter (currently 10). > > The PostgreSQL documentation contains more information about > > configuring your system for PostgreSQL. > > > > Since I am using Solaris OS, I may need to reboot the machine to change > > the value of semmns. I don't want to do that. How can I reduce the > > parameter max_connections and rerun the command? > > initdb has already reduced it as far as is sane, if not further. (The > underlying implementation is such that values less than 16 don't > actually reduce the number of semaphores we acquire.) You'll have to do > whatever is needed to fix the kernel limit. Don't forget to look at the > shared memory limit while you're at it; it's likely that that's > unreasonably small, too. > > regards, tom lane You might not need to reboot. You can change resource limits with the prctl command. Check out the posts by t_altamimi on this thread http://forums.oracle.com/forums/message.jspa?messageID=1077746 Luca ---(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: [GENERAL] installation problem - semaphores
Yadu <[EMAIL PROTECTED]> writes: > selecting default max_connections ... 10 > selecting default shared_buffers ... 50 > creating configuration files ... ok > creating template1 database in /usr/local1/postgres/data/base/1 ... FATAL: > could not create semaphores: No space left on device > DETAIL: Failed system call was semget(1, 17, 03600). > HINT: This error does *not* mean that you have run out of disk space. > It occurs when either the system limit for the maximum number of > semaphore sets (SEMMNI), or the system wide maximum number of semaphores > (SEMMNS), would be exceeded. You need to raise the respective kernel > parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by > reducing its max_connections parameter (currently 10). > The PostgreSQL documentation contains more information about > configuring your system for PostgreSQL. > Since I am using Solaris OS, I may need to reboot the machine to change the > value of semmns. I don't want to do that. How can I reduce the parameter > max_connections and rerun the command? initdb has already reduced it as far as is sane, if not further. (The underlying implementation is such that values less than 16 don't actually reduce the number of semaphores we acquire.) You'll have to do whatever is needed to fix the kernel limit. Don't forget to look at the shared memory limit while you're at it; it's likely that that's unreasonably small, too. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Grouping aggregate functions
On Sun, Apr 02, 2006 at 04:03:03AM -0700, Richard Connamacher wrote: > I've got a question, if anyone can help me out. I know how to use an > aggregate function to, say, find the lowest price ever listed for a > product. I also know how to combine that with a SELECT ... GROUP BY > statement to find, say, the lowest price reported for each month. > Now, what if I want to find the *average* of all the lowest prices > for each month? Plopping that SELECT statement inside parentheses and > inside an "avg( )" function produces an error. Use a subquery. ie.e not: > SELECT avg( ( SELECT min(price) FROM weekly_supply_prices GROUP BY > month ) ) But SELECT avg(minprice) FROM (SELECT min(price) as minprice FROM weekly_supply_prices GROUP BY month ); > Anyone have any idea how to do this? Or do I have to compute the > average in another program? Use SQL to calculate both :) One way to think about it is by think of the subquery producing a temporary table which you then use in another query. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] Grouping aggregate functions
Hey all, I'm new on this list, and have been playing with Postgres a lot this week. (Love it, by the way.) I've got a question, if anyone can help me out. I know how to use an aggregate function to, say, find the lowest price ever listed for a product. I also know how to combine that with a SELECT ... GROUP BY statement to find, say, the lowest price reported for each month. Now, what if I want to find the *average* of all the lowest prices for each month? Plopping that SELECT statement inside parentheses and inside an "avg( )" function produces an error. What I'd love to do, and which creates an error, would be something like: SELECT avg( ( SELECT min(price) FROM weekly_supply_prices GROUP BY month ) ) The error I get is: "ERROR: more than one row returned by a subquery used as an expression" (to state the obvious). If I don't double up the quotes I get a syntax error. Anyone have any idea how to do this? Or do I have to compute the average in another program? Thanks! Richard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL makes me lie
joseph wrote: > 2006-04-01 (토), 21:15 -0500, Robert Treat 쓰시길: >> On Saturday 01 April 2006 17:26, Brendan Duddridge wrote: ... >> Improved collation support is being worked on but it's a complex problem so >> there's no realt ETA. (Developers interested in helping out our encouraged >> to >> send a note to -hackers). In the mean time check out the citext project: >> http://gborg.postgresql.org/project/citext/projdisplay.php >> > Does this citext data type also provide case insensitive matching for > utf8 characters, as the gentleman's frontbase collation command did? > Well, if you can read :-) http://gborg.postgresql.org/project/citext/faq/faq.php?faq_id=105 Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL makes me lie
2006-04-01 (토), 21:15 -0500, Robert Treat 쓰시길:
> On Saturday 01 April 2006 17:26, Brendan Duddridge wrote:
> > We used to use FrontBase for our databases, but we have since
> > switched to PostgreSQL for
> > performance reasons. However, FrontBase did have very nice collation
> > support.
> >
> > To get case insensitive searches (even on UTF-8 data), all you had to
> > do was alter the column
> > and set its collation to 'CASE_INSENSITIVE' like so:
> >
> > alter column "test"."Column1" to collate
> > "INFORMATION_SCHEMA"."CASE_INSENSITIVE";
> >
> > It would be VERY nice if PostgreSQL supported this as it would easily
> > allow you to write
> > case insensitive queries that use an index like:
> >
> > select * from TEST where column1 like 'SOme ValUe%' or even just use
> > the equals operator
> > for an exact match yet still case insensitive.
> >
> > Right now to get the same effect we create an index using a function
> > like 'lower(some_column)'.
> > But that requires us to write our queries like:
> >
> > select * from test where lower(column1) like lower('SOme ValUe%');
> >
> >
> > Any ideas if better collation support is in the plans for future
> > versions of PostgreSQL?
> >
>
> Improved collation support is being worked on but it's a complex problem so
> there's no realt ETA. (Developers interested in helping out our encouraged to
> send a note to -hackers). In the mean time check out the citext project:
> http://gborg.postgresql.org/project/citext/projdisplay.php
>
Does this citext data type also provide case insensitive matching for
utf8 characters, as the gentleman's frontbase collation command did?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] installation problem - semaphores
Hi,I have installed postgre-sql in directory /usr/local1/postgresNow, I am trying to run /usr/local1/postgres/bin/initdb -D /usr/local1/postgres/dataI got following error.> initdb -D /usr/local1/postgres/data The files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with locale C.fixing permissions on existing directory /usr/local1/postgres/data ... ok creating directory /usr/local1/postgres/data/global ... okcreating directory /usr/local1/postgres/data/pg_xlog ... okcreating directory /usr/local1/postgres/data/pg_xlog/archive_status ... okcreating directory /usr/local1/postgres/data/pg_clog ... ok creating directory /usr/local1/postgres/data/pg_subtrans ... okcreating directory /usr/local1/postgres/data/base ... okcreating directory /usr/local1/postgres/data/base/1 ... okcreating directory /usr/local1/postgres/data/pg_tblspc ... okselecting default max_connections ... 10selecting default shared_buffers ... 50creating configuration files ... okcreating template1 database in /usr/local1/postgres/data/base/1 ... FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(1, 17, 03600).HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about configuring your system for PostgreSQL.child process exited with exit code 1initdb: removing contents of data directory "/usr/local1/postgres/data"Since I am using Solaris OS, I may need to reboot the machine to change the value of semmns. I don't want to do that. How can I reduce the parameter max_connections and rerun the command?Thank you,Yadnyesh. -- God is in details; specialization is god.
Re: [GENERAL] PostgreSQL makes me lie
joseph wrote: ... > I was on the postgres lists about 1 1/2 years ago, since then i haven't > been on then. when there was a question about modifying the way > postgres does collation and supports character sets on a database (as > opposed to table, or column-level manner) there was much clammer from > the developers about how that would break all past applications and it > was too difficult to program, but as i said, it's been a while. it > sounds like it's teh same. the developers are annoyed with constant > reminders of their flawed scheme and there is no timetable for a change > yet (that i know of). > qualifications noted. You should read the archives for the 1 1/2 years you missed. Things have greatly changed. Ah yes, the option with a custom datatype with collation emulation was possible any time :-) Kind regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
