Re: [GENERAL] How to obtain info about the user?
*SELECT* pg_has_role() , has_table_privilege(), has_database_privilege(), has_function_privilege On Thu, Jul 10, 2008 at 1:15 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Thu, 2008-07-10 at 11:08 -0500, Ismael Almaraz Ezparza wrote: Hi there, I'm connecting to postgres from java using the postgres driver... and I need to get the info about the user that is connected to the DB, lets say for example: name, groups, permisions (select, update, execute)... Does anyone knows how can I get that info? Also, if the user is in the group admin, I need to geat a list of users and the permissions of each... just like pgadmin3 does... I suggest you turn on query logging to see how pgadmin3 does it. Joshua D. Drake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- William Leite Araújo Pai 0.6 beta 2.1 Dizem que agora melhora...
Re: [GENERAL] xml and postgresql
On Fri, Jul 4, 2008 at 11:01 AM, Gwyneth Morrison [EMAIL PROTECTED] wrote: aravind chandu wrote: Hi folks, I need to load xml data in to database can you tell me they way how do I import xml data into postgresql database. lets dat this is the xml file bookstore book category=CHILDREN Why category is not a Element of book, as all others? Is it a column of table bookstore? titleHarry Potter/title authorJ K. Rowling/author year2005/year price29.99/price /book book category=WEB titleLearning XML/title authorErik T. Ray/author year2003/year price39.95/price /book /bookstore so finally the table name should be bookstore and column names are category,title, author,year,price and all the information in the xml file should be ported to the table can you please tell me how to do this its a bit confusing to me as i am just a beginner to this. Thanks in advance, Aravind. I have written a python program that may help you. It is part of a larger project I hope to post in a few days. Or I could sent it to you. Gwyneth -- William Leite Araújo Pai 0.6 beta 2.1 Dizem que agora melhora...
Re: [GENERAL] Find smallest common year
If I understood your question, maybe it's you want: SELECT min(desired data) FROM table1 JOIN table2 ON (table1.data_field= table2.data_field); 2007/9/27, Stefan Schwarzer [EMAIL PROTECTED]: Hi there, my aim is to plot a line graph for a single country but for two or three variables. Now, one could use two different y-axis (if there are two data sets with different units); but it's not really the right way to analyse data. One should rather normalize them to a base year to enhance comparisons. To achieve this, one would set the values for a specific year to 1 (or 100) and put the other values of all variables in relation to it. Thus, taking two variables, say, Fish Catch - in Thousand Metric Tons - data available for 1975 to 2005 and Total Population - in Million People - data available for 1960 to 2005 Country is Kenya. The smallest common year would be 1975. One needs thus to set the values for that year to 1 (or 100), and calculate the other values of the variables in respect to the needed factor. Now the question is how to find that smallest common year automatically, via SQL or PHP? Can anyone give me a hint? Thanks for any help! Stef *Stefan Schwarzer* Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEP Data Portal: http://geodata.grid.unep.ch -- William Leite Araújo Estudante de paternidade - 24a semana
Re: [GENERAL] postgres and webmin
2007/6/27, Danyelle Gragsone [EMAIL PROTECTED]: Greetings, I am trying to setup postgres and postfix using webmin. I am following this guide: http://gentoo-wiki.com/HOWTO_Email:_A_Complete_Virtual_System_-_Postfix_to_Postgres I received the following in the postgres db section of webmin: select * from pg_database order by datname failed : no pg_hba.conf entry for host [local], user postgres, database template1, SSL off I sadly have no clue on how to go by fixing this. Please help :) It talks about this error in the guide. But it really does not tell you how to fix it specifically. I don't even see a section in the area specified for the authentication they are suggesting. Operating systemGentoo Linux Webmin version 1.350 I believe the problem is in this section. # local is for Unix domain socket connections only # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database # super user can access the database using some other method. # Noninteractive # access to all databases is required during automatic maintenance # (autovacuum, daily cronjob, replication, and similar tasks). # # Database administrative login by UNIX sockets local all postgres ident sameuser host all all 10.2.0.202 255.255.255.255 trust # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: hostall all ::1/128 trust I could be wrong. Please help the newb. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- William Leite Araújo Estudante de paternidade - 13a semana
Re: [GENERAL] replace function in a query
http://www.postgresql.org/docs/8.0/interactive/functions-string.html http://www.postgresql.org/docs/8.1/interactive/functions-string.html http://www.postgresql.org/docs/8.2/interactive/functions-string.html 2007/5/21, Harpreet Dhaliwal [EMAIL PROTECTED]: Hi, I have to write a query that does the following. select column_x from tbl_xyz where column_y = 'abc def ghi' new line characters i.e. '\n' in column_y should be replaced with a space character i.e. ' ' in this query. Is there any replace function that when used in the query with colulmn_y would do it? Thanks, ~Harpreet -- William Leite Araújo Analista de Banco de Dados - QualiConsult
Re: [GENERAL] Trigger function which inserts into table; values from lookup
Maybe you can use a LEFT OUTER JOIN ... CREATE or REPLACE FUNCTION public.tproc_handle_item_active() RETURNS pg_catalog.trigger AS $BODY$ DECLARE rec_item record; int_org_id integer; BEGIN -- whenever an item is set active; create entries in the following table: -- t_koaitem if new.item_active = true and old.item_active = false then select * into rec_item from t_item where item_id = new.item_id; int_org_id = rec_item.item_org_id; insert into t_koaitem (koai_koa_id, koai_item_id, koai_item_locked, koai_user_idm) SELECT t_koa.koa_id, t_item.item_id, false as lockstatus, t_item.item_user_idm FROM t_item INNER JOIN t_koa ON t_item.item_org_id = t_koa.koa_org_id LEFT OUTER JOIN t_koaitem ON (koaitem_koa_id = t_koa.koa_id AND koaitem_item_id = t_item.item_id) WHERE (((t_item.item_active)=True) AND ((t_koa.koa_koastatus_id)=2 Or (t_koa.koa_koastatus_id)=3) AND ((t_item.item_org_id)=int_org_id) AND (t_koaitem.koaitem_item_id IS NULL AND koaitem_item_id IS NULL) ); end if; return null; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; 2007/5/21, novnov [EMAIL PROTECTED]: No and update would not be needed; but the capability would be close enough, I'd just skip the update, do nothing for that record. But from the sound of it, the example you're suggesting involves a loop or something of that order. I could have written this using a loop but thought a bulk operation that essentially worked like insert new rows for the set and while doing so, silently skip inserts which would cause dupe key violations. I explained all of this in the earlier messages. I thought it might be more effenient to handle without a loop. I've been able to do this kind of thing with other databases; essentially instruct the routine to ignore errors silently, commit what it can commit. Raymond O'Donnell wrote: On 21/05/2007 05:26, novnov wrote: OK, but, how do I set this up to do what I need? I want an insert that would create a dupe key to be rolled back, and inserts that would not create dupe keys to be committed. Do you specifically need it in a trigger? I seem to recall an example in the docs for pl/pgsql demonstrating a function to do something like this - I think it tries an INSERT, and when a duplicate key raises an exception, it does an update instead. - You could easily adapt this to your purposes. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10720190 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- William Leite Araújo Analista de Banco de Dados - QualiConsult
Re: [GENERAL] Having a problem with my stored procedure
2007/2/13, Laura McCord [EMAIL PROTECTED]: To make a long story short, I am archiving data from an original table to a table I created. This is a third party web application that I am doing this with, so I can't revise the structure/code of this application. With this said, if the original table goes through an insert or update action I want to replicate the information to my archive table. I don't want to delete any articles from my archive table so this is why I am not wanting to do anything based on a delete action. The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred and an insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based on an insert or update. Help, I've been stumped for two days. Thanks in advance. This is what I have so far: CREATE TRIGGER archive_articles AFTER INSERT OR UPDATE ON news_content EXECUTE PROCEDURE su_archive_articles(); CREATE OR REPLACE FUNCTION su_archive_articles() RETURNS TRIGGER LANGUAGE plpgsql AS ' DECLARE tmp_news_id CHARACTER varying(48); tmp_title CHARACTER varying(100); tmp_abstract CHARACTER varying(300); tmp_news_story TEXT; tmp_topic_id CHARACTER varying(10); tmp_create_date DATE; tmp_author CHARACTER varying(50); tmp_begin_date DATE; tmp_end_date DATE; tmp_priority CHARACTER(1); tmp_image_name CHARACTER varying(512); tmp_image_mime_type CHARACTER varying(50); tmp_layout_type CHARACTER varying(10); BEGIN SELECT INTO tmp_news_id news_id from news_content where last_inserted(news_id); SELECT INTO tmp_title title from news_content where last_inserted(news_id); SELECT INTO tmp_abstract abstract from news_content where last_inserted(news_id); SELECT INTO tmp_news_story news_story from news_content where last_inserted(news_id); SELECT INTO tmp_topic_id topic_id from news_content where last_inserted(news_id); SELECT INTO tmp_create_date create_date from news_content where last_inserted(news_id); SELECT INTO tmp_author author from news_content where last_inserted(news_id); SELECT INTO tmp_begin_date begin_date from news_content where last_inserted(news_id); SELECT INTO tmp_end_date end_date from news_content where last_inserted(news_id); SELECT INTO tmp_priority priority from news_content where last_inserted(news_id); SELECT INTO tmp_image_name image_name from news_content where last_inserted(news_id); SELECT INTO tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id); SELECT INTO tmp_layout_type layout_type from news_content where last_inserted(news_id); IF TG_OP = 'INSERT' THEN //This is to be done if an INSERT action was done on the table INSERT INTO su_archives(news_id, title, abstract, news_story, topic_id, create_date, author, begin_date, end_date, priority, image_name, image_mime_type, layout_type) VALUES (tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_ image_name ,tmp_image_mime_type,tmp_layout_type); ELSEIF TG_OP = 'UPDATE' THEN //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE END IF; RETURN NEW; END '; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- William Leite Araújo Analista de Banco de Dados - QualiConsult
Re: [GENERAL] A VIEW mimicing a TABLE
On 12/13/06, Rafal Pietrak [EMAIL PROTECTED] wrote:
Hi,
May be someone could help me with this:
For some time now, I exercise the use of VIEWs to expose just the
features of TABLES a particular user is supposed to see/have.
I can see that with a VIEW, I can do prity mutch everything I can do
with a TABLE, so a VIEW mimics a TABLE quite well but one feature: a
default value for a row on INSERT.
Here is the case. I have:
CREATE TABLE logfile (id serial,
tm timestamp default current_timestamp,
info text);
When I: INSERT INTO logfile (info) VALUES ('hello');
I get ID and TM fields filled up for me by postgres. But when I:
INSERT INTO logfile (id,tm,info) VALUES (NULL, NULL, 'hello'); I have
'overridden' the defaults with NULL values - sometimes this is
desirable.
Now, I cannot really figure out any way to do that with a VIEW:
CREATE VIEW logview AS SELECT * FROM logfile;
CREATE RULE new_entry AS ON INSERT to logview DO INSTEAD INSERT
(id,tm,info) VALUES (new.id,new.tm,new.info);
CREATE RULE new_entry_noid AS ON INSERT to logview WHERE new.id IS NULL
DO INSTEAD INSERT (tm,info) VALUES (new.tm,new.info);
CREATE RULE new_entry_notm AS ON INSERT to logview WHERE new.tm IS NULL
DO INSTEAD INSERT (id,info) VALUES (new.id,new.info);
CREATE RULE new_entry_notm AS ON INSERT to logview WHERE new.tm IS NULL
AND new.id IS NULL DO INSTEAD INSERT (info) VALUES (new.info);
All can be done with:
CREATE RULE new_entry AS ON INSERT to logview DO INSTEAD INSERT
(id,tm,info) VALUES (COALESCE(new.id,[default]),COALESCE(new.tm
,[default]),COALESCE(new.info,[default]));
Which is overtalkative, but sort of works.
Sort of, because new.tm IS NULL is not actually new.tm was not
provided. When it *was*provided*, but its value was NULL, the VIEW
behaves differently then the TABLE.
Is there a way, to make such VIEW behave *exactly* as the TABLE does?
--
-R
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
William Leite Araújo
Analista de Banco de Dados - QualiConsult
Re: [GENERAL] A VIEW mimicing a TABLE
14 Dec 2006 02:45:12 -0800, SunWuKung [EMAIL PROTECTED]: CREATE RULE new_entry AS ON INSERT to logview DO INSTEAD INSERT (id,tm,info) VALUES (COALESCE(new.id,[default]),COALESCE(new.tm ,[default]),COALESCE(new.info,[default])); what would [default] insert here? A constant, a function, anything you want. I have a function to create rules of insert an update on views that have the same columns that the tables that its represent. In this case, my [default] is the default value for the column of the table. COALESCE function only choose the second argument when the first is null. An alias to IF $1 IS NULL THEN $2 ELSE $1. the default of the view or the default of the underlying table? B. ---(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 -- William Leite Araújo Analista de Banco de Dados - QualiConsult
Re: [GENERAL] How to clone a table so that primay and foreign keys remain
http://www.alberton.info/postgresql_meta_info.html 2006/11/24, Andrus [EMAIL PROTECTED]: I need to clone a schema in a database programmatically. Using pg_dump.exe to dump chema in plain text, then rename old schema and after that use pg_restore.exe to restore schema is slow since I need to clone data only in some smaller tables. Most of tables should be cloned without data. To start this I think I need to clone all schema tables first. How to create copy of table so that primary keys and foreign keys relationships remain ? I tried CREATE TABLE clone AS SELECT * FROM original But this creates plain table. Any idea ? Or where to find sample (simpler that pgAdmin or pg_dump source code) which re-constructs statements to create primary and foreign keys ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- William Leite Araújo Analista de Banco de Dados - QualiConsulte
Re: [GENERAL] Why overlaps is not working
2006/11/9, Andrus [EMAIL PROTECTED]:
They're adjacent, they don't overlap. Check the documentation on OVERLAPS, I'm sure it's explicit about whether it is inclusive or exclusive (the latter apparently).8.2 doc does not explain term overlap. It only says:
This _expression_ yields true when two time periods (defined by theirendpoints) overlap How to make overlaps to return correct result?select 1 where ('2006-10-30'::date, '-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);In real queries I have column names and parameters instead of dataconstants.The only way it seems to replace OVERLAPS operator with AND, OR, =
operators.Is it so ?Andrus. Maybe: ('2006-10-16'::DATE BETWEEN '2006-10-30'::date AND '-12-31'::date) OR ('2006-10-31'::DATE BETWEEN '2006-10-30'::date AND '-12-31'::date)
-- William Leite Araújo
Re: [GENERAL] FOR ... IN
2006/11/6, Alain Roger [EMAIL PROTECTED]: Hi,Before (in version 8.0.1), i did the following thing and it was working well...now (in version 8.1.4) it seems that it does not work anymore...problem is with FOR rec IN loop...So how can i tell FOR all RECORDS from select * from articles, articletypes, department where ... LOOP ... ? thanks,Al.CREATE OR REPLACE FUNCTION public.SP_U_001 (TypeOfArticle varchar) RETURNS SETOF public.active_articles AS$body$DECLARE TypeArt VARCHAR := $1; rec RECORD; res active_articles;/**/BEGIN FOR rec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP res.article_type := rec.articletypes.articletype_type; res.article_author := rec.articles.author; res.department_owner := rec.department.department_name; res.department_picture := rec.department.department_picture; res.article_title := rec.articles.title; res.article_content := rec.articles.content; res.date_creation := rec.articles.creation_date; res.date_start := rec.articles.validity_period_start; res.date_end := rec.articles.validity_period_end; RETURN NEXT res; END LOOP; RETURN; END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Never forgot of return before END procedure;-- William Leite Araújo
Re: [GENERAL] FOR ... IN
2006/11/7, Alain Roger [EMAIL PROTECTED]: but there is already a RETURN NEXT res;so what will be the point of this RETURN after the END LOOP; ?http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html -- William Leite Araújo
Re: [GENERAL] Schema search_path and views
2006/11/6, Just Someone [EMAIL PROTECTED]: I have a database with multiple schemas all with the same structure(but of course different data...).I want to create a view that will be created in a shared schema, andwhen executed will be executed against the current schema. Whenever I try it, it seems the view is linked to a specific schema used whencreating it, and doesn't reevaluates based on the current schema.Here is the pseudo structure/code:schema1:===create table t1 ... schema2:===create table t1 ...shared_schema:create table t3 ...create the view:===set search_path to shared_schema, schema1;create view view1 as select * from t1; In this point, your view was created with thi code: CREATE VIEW view1 AS SELECT t1.column1, t1.column2, ..., t1.columnN FROM schema1.t1; try the view:set search_path to shared_schema, schema1;select * from view1;set search_path to shared_schema, schema2;select * from view1;Results:==In the above, both select * from view1; will return the same data, though the search path changed.Is there a way to make the view use the current search_path?--Family management on rails: http://www.famundo.com - coming soon! My development related blog: http://devblog.famundo.com---(end of broadcast)---TIP 6: explain analyze is your friend -- William Leite Araújo
Re: [GENERAL] more than 32 parameters to a function?
Create a type whith the fields?! Ex.: Create type myType as (field1 integer, field2 integer, ...); Create or replace function func_type(myType) returns integer AS $$ DECLARE param ALIAS FOR $1; BEGIN RAISE LOG 'Param fields: %, %, %, ... ', param.field1, param.field2, ...; END; $$ LANGUAGE plpgsql; 2006/10/26, Ottavio Campana [EMAIL PROTECTED]: I'm writing some stored procedures in pl/pgsql for a database usingpostgresql 7.4.7.I need to write a complex function with 65 arguments, but when I try torun it I get an error complaining that arguments can be up to 32. Is there a way to solve this problem or do I have to try to split thefunction into three new ones?--Non c'e' piu' forza nella normalita', c'e' solo monotonia. -- William Leite Araújo
Re: [GENERAL] Difficulty passing in an array of values to EXECUTE
2006/10/25, Richard Huxton dev@archonet.com: Nick Rowlands wrote: Hi there, I'm having trouble creating a function using plpgsql. I cannot pass the array 'productids' to the ANY construct of the IN EXECUTE SELECT statement. Any ideas on what I'm doing wrong would be most appreciated. Here's the function: FOR row IN EXECUTE 'SELECT p.id, sku, description, base_price, j.name AS jname, r.name AS rname FROM products2 p INNER JOIN jewellery_types j ON j.id = p.jewellery_type INNER JOIN ranges r ON r.id = p.range_id WHERE p.id = ANY( '||array_to_string(productids,',')||') '|| filter_jewellery LOOP EXECUTE takes a string, so you're giving it the word productids notthe contents of the variable with that name.You'll need to generate a string containing comma-separated values (orthe array definition) and use that. -- Richard Huxton Archonet Ltd---(end of broadcast)---TIP 5: don't forget to increase your free space map settings -- William Leite Araújo
Re: [GENERAL] Text retrieval data mining features in PostgreSQL
Maybe pg_trgm contrib.-- William Leite Araújo
Re: [GENERAL] Temp Tables
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]: (..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgres-- William Leite AraújoEspecialista em Geoprocessamento- UFMG Bacharel em Ciêncida da Computação - UFMGMSN:[EMAIL PROTECTED]ICQ:222159351GTalk: [EMAIL PROTECTED]Yahoo: [EMAIL PROTECTED]Skype: william.bh
Re: [GENERAL] Temp Tables
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]: (..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgresql version? Why you need return the temporary table type? Why create a temporary table if you use a function return type setof? -- William Leite Araújo
Re: [GENERAL] plpgsql replication stored procedure
On 4/17/06, Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: I'm trying to write a stored procedure that can capture all the changes to a table and record the changes based on the table's primary key. I can almost get there but the sticking point is being able to access the primary key field of the NEW/OLD record in the trigger stored procedure without knowing it's name. My database has a table that store all changes mades on all tables. The insert is made by onde single trigger, but I create a function that build functions to convert an generic record on a text value. If this is usefull, mail-me ;-) Dynamic record name cannot be made on a plpgsql function. This topic was discursed some months ago.-- William Leite AraújoEspecialista em Geoprocessamento- UFMGBacharel em Ciêncida da Computação - UFMG MSN:[EMAIL PROTECTED]ICQ:222159351GTalk: [EMAIL PROTECTED]Yahoo: [EMAIL PROTECTED] Skype: william.bh
Re: [GENERAL] How to delete all operators
You can make a function to do this. CREATE FUNCTION drop_operators(text) RETURNS BOOL AS $$ DECLARE op record; BEGIN FOR op IN SELECT opname FROM pg_operator as o left join pg_namespace as n on (o.oprnamespace = n.oid) WHERE nspname = $1 LOOP EXECUTE 'DROP OPERATOR '||quote_literal(op.opname)|| ';'; END LOOP; RETURN TRUE; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; And so: SELECT drop_operators( 'public');On 3/31/06, Martin Pohl [EMAIL PROTECTED] wrote: Hi,I have a database with operators and functions in plpgsql.To update the data to the latest version I would like to drop all operators.There might be some, that I don't know. I don't have access to the database, but have to write a script, that will update the data.Is there any way to drop all operators (given they are all in the schemapublic) in a script?Something like (pseudocode):Drop all operators in schema public Thanks in advance for answers--E-Mails und Internet immer und überall!11 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb ---(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-- William Leite Araújo
Re: [GENERAL] Which error constant to use for permission deny error when updating a table that user is not allowd to
Why doesn't test before update? IF EXISTS( SELECT * FROM information_schema.table_privileges WHERE grantee = '1' AND table_name = '2' AND privilege_type = 'UPDATE') THEN ... ELSE ... END IF;On 4/3/06, Emi Lu [EMAIL PROTECTED] wrote: Hello,I am trying to catch permission denied exception. For example, user 1 isnot allowed to update table 2, when user1 updated table 2 there is apermission excetpion.In http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html,I tried modifying_sql_data_not_permitted , sql_routine_exception,external_routine_exception but none of them catch the permission deny error.Could someone tell me which error constant should I use to catch thepermission denied error please?Thanks a lot!Ying---(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-- William Leite Araújo
[GENERAL] Server deadlock?
I have 2 tables, A and B. Onde update on A fires a trigger Ta that insert by dblink_exec on B. B has an insert trigger Tb that print a message before line RETURN NEW; command of Tb, but Ta call to dblink_exec doesn't return never... Any idea?tank's a lot-- William Leite Araújo
Re: [GENERAL] Strange startup error
pg_ctl status [-D /postgres/data/dir]On 3/17/06, Andrew Stewart [EMAIL PROTECTED] wrote: I have had postgres working perfectly up until recently.Now suddenly Iget an error when trying to run postgres:% psqlpsql: FATAL:No pg_hba.conf entry for host localhost, user postgres,database postgres % postmaster LOG:could not bind Unix socket: Address already in useHINT:Is another postmaster already running on port 5432? If not,remove socket file /tmp/.s.PGSQL.5432 and retry. WARNING:could not create Unix-domain socketFATAL:could not create shared memory segment: Cannot allocate memoryDETAIL:Failed system call was shmget(key=5432002, size=3768320, 03600).HINT:This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce therequest size (currently 3768320 bytes), reduce PostgreSQL'sshared_buffers parameter (currently 300) and/or its max_connectionsparameter (currently 50). The PostgreSQL documentation contains more information aboutshared memory configuration.Anyone have an ideas?-Andrew Stewart---(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 -- William Leite Araújo
Re: [GENERAL] return setof and Temp tables
2006/2/17, Justin B. Kay [EMAIL PROTECTED]: I have looked around and found that you can use return setof in a function to return a result set, but can you use a temp table as the setof target? Yes, you can ( PostgreSQL 8.0.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13))! I build a temp table using various select statements and then try to return the result as a recordset. I get an error: type t1 does not exist. Please, send the sample. I test Create temp table AS SELECT * FROM setof function and works on 8.0.6-- William Leite Araújo
[GENERAL] type int2vector
Hi all, Anyone has or knows a function to manipulate/work with int2vector type? Tank's-- William Leite Araújo
[GENERAL] Rules on 8.0.2
Hi, I'm trying create the rule : CREATE OR REPLACE RULE select_public_cliente AS ON SELECT TO public.cliente_tipo WHERE NOT EXISTS ( SELECT usesysid FROM pg_user left join pg_group on ( idx( grolist, usesysid ) 0 ) WHERE usename = current_user AND groname SIMILAR TO 'assinatura' ) DO INSTEAD SELECT clt_dsc_tipo FROM public.cliente_tipo; Its to limit users to see only one column on table. But I've the error: ERROR: event qualifications are not implemented for rules on SELECT The version is: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-5) Any idea? -- William Leite Araújo
[GENERAL] alter table schema on 8.0.X
Hi, I'm trying alter a table schema on 'PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-5)'. On 8.1, by documentation, has the commant ALTER TABLE table SET SCHEMA new_schema. Any solution for this in 8.0? Ps.: solutions different of CREATE TABLE new_schema.table as SELECT * FROM schema.table please.-- William Leite Araújo
[GENERAL] TSearch2 snowball version error
Hi, I'm trying compile a new brazilian portuguese dictionary to TSearch2 contrib, but found the errors: portuguese_stem.c: In function `r_prelude': portuguese_stem.c:481: error: void value not ignored as it ought to be portuguese_stem.c:487: error: void value not ignored as it ought to be portuguese_stem.c: In function `r_postlude': portuguese_stem.c:610: error: void value not ignored as it ought to be portuguese_stem.c:616: error: void value not ignored as it ought to be portuguese_stem.c: In function `r_standard_suffix': portuguese_stem.c:662: error: void value not ignored as it ought to be portuguese_stem.c:672: error: void value not ignored as it ought to be portuguese_stem.c:682: error: void value not ignored as it ought to be portuguese_stem.c:692: error: void value not ignored as it ought to be portuguese_stem.c:702: error: void value not ignored as it ought to be portuguese_stem.c:715: error: void value not ignored as it ought to be portuguese_stem.c:729: error: void value not ignored as it ought to be portuguese_stem.c:744: error: void value not ignored as it ought to be portuguese_stem.c:760: error: void value not ignored as it ought to be portuguese_stem.c:775: error: void value not ignored as it ought to be portuguese_stem.c:791: error: void value not ignored as it ought to be portuguese_stem.c:806: error: void value not ignored as it ought to be portuguese_stem.c:818: error: void value not ignored as it ought to be portuguese_stem.c:832: error: void value not ignored as it ought to be portuguese_stem.c: In function `r_verb_suffix': portuguese_stem.c:856: error: void value not ignored as it ought to be portuguese_stem.c: In function `r_residual_suffix': portuguese_stem.c:880: error: void value not ignored as it ought to be portuguese_stem.c: In function `r_residual_form': portuguese_stem.c:902: error: void value not ignored as it ought to be portuguese_stem.c:929: error: void value not ignored as it ought to be portuguese_stem.c:935: error: void value not ignored as it ought to be portuguese_stem.c: In function `portuguese_ISO_8859_1_stem': portuguese_stem.c:993: error: void value not ignored as it ought to be make: ** [portuguese_stem.o] Erro 1 This after change the included file header.h, the old ../runtime/header.h is invalid. I think that is a version trouble, because the return of functions slice_from_s and slice_del are int in current snowball portuguese files, but on include files of version 8.0.2 and 8.0.3 of Portgresql the return type is void. Help-me, please. -- William Leite Araújo
