This is relevant to all versions.
The copy command gave an error message that it was not able to write
to the output file.
ERROR: COPY command, running in backend with effective uid 40, could not open file '/home/xxx/clients' for writing. Errno = Permission denied (13).
When this occurs, you should also check the permission on the file it is trying to write to and use chmod +w to add write privileges if necessary.
Note that copy is the back end copy. The file named there must be accessible and writable by the server process. If the server is on another machine the path should be relative to that machine.
If you want a copy to the client, use \copy from psql (or another client) and give the file name relative to the client.
A request was made for a "translation" of an Interbase function into PostgreSQL. The translation also shows how to modify an attribute before returning.
NOTE: The translation uses the PostgreSQL 7.3 specific feature of returning rows from a query. If there were no need to return the row set, the function would work fine returning a success value on other PostgreSQL releases.
The Interbase function:
CREATE PROCEDURE MY_PROC( P_DATE_START TIMESTAMP ) RETURNS ( R_DATE_PAYMENT TIMESTAMP, R_DESCRIPTION VARCHAR(50), R_VALUE NUMERIC (12,2)) AS BEGIN FOR SELECT DATE_PAYMENT, R_DESCRIPTION,VALUE FROM PAYMENTS WHERE DATE_PAYMENT BETWEEN :P_DATE_STAR AND 'TODAY' INTO :R_DATE_PAYMENT , :R_DESCRIPTION , :R_VALUE DO BEGIN /*here I can do any check for each row of SELECT below!! */ SUSPEND; /* Each SUSPEND command returns a row */ END END I can use this stored proc like this: SELECT * FROM MY_PROC( '01/01/2002' )
The PostgreSQL translated function:
CREATE TABLE payments (r_date_payment TIMESTAMP, r_description VARCHAR(50), r_value numeric (12,2)); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'a', '12.50'); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'b', '11.75'); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'c', '-99.99'); CREATE OR REPLACE FUNCTION my_proc(TIMESTAMP) RETURNS SETOF payments AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT * FROM payments WHERE r_date_payment BETWEEN $1 AND CURRENT_TIMESTAMP LOOP IF rec.r_value < 0 THEN rec.r_value = rec.r_value*-1; END IF; RETURN NEXT rec; /* Each RETURN NEXT command returns a row */ END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; test=# select * from payments; r_date_payment | r_description | r_value ----------------------------+---------------+--------- 2002-10-22 10:27:38.086554 | a | 12.50 2002-10-22 10:27:38.172964 | b | 11.75 2002-10-22 10:27:38.177543 | c | -99.99 (3 rows) test=# SELECT * FROM my_proc('01/01/2002'); r_date_payment | r_description | r_value ----------------------------+---------------+--------- 2002-10-22 10:27:38.086554 | a | 12.50 2002-10-22 10:27:38.172964 | b | 11.75 2002-10-22 10:27:38.177543 | c | 99.99 (3 rows)
The CASE statement in SQL is meant to return a value based on a condition, not to perform an action based on a condition. To get an action based on a condition, you should try plpgsql or another procedural language.
The following SQL will not work and will probably delete from cart_id 10 if the condition were true in, say cart_id 7.
select member_id, case when (select count(buy_later) from cart_li where member_id=cart_id AND buy_later=true) > 0 then (delete from cart_li where cart_id=10) end from carts
Instead use the DELETE statement with the proper condition. For example:
delete from cart_li where exists (select 1 from cart, cart_li c2 where cart_li.cart_id=member_id and c2.cart_id=member_id and c2.buy_later=true);
The question was about two tables with a master detail relationship, where there may be one or more details per master. The question was how to print out all of the details for a given master in on column.
m1 d1 m2 d1, d2, d3
This calls for an aggregate and Holger Kawitter came up with an excellent example based on the tables in the question. I've included the whole scenario recreation script here.
create table content ( content_id integer, material_id integer, percentage text); create table material ( material_id integer, name text); insert into material values (1,'COTTON'); insert into material values (2,'POLYESTER'); insert into material values (3,'NYLON'); insert into material values (4,'SPANDEX'); insert into content values (1,1,'100%'); insert into content values (2,2,'63.5%'); insert into content values (2,3,'31.5%'); insert into content values (2,4,'5%'); select * from content; select * from material; create function xstr_append( text, text ) returns text as ' begin if $1 isnull then return $2; else return $1 || '' '' || $2; end if; end;' language 'plpgsql'; create aggregate xstr_concat ( basetype = text, sfunc = xstr_append, stype = text ); select xstr_concat( c.percentage || ' ' || m.name ) from content c, material m where c.material_id = m.material_id group by c.content_id;
Clients, in this case Perl/CGI, receive "notices" on STDOUT about implicit actions from DDLs (create, drop, etc.). For example:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lock_dets_pkey' for table 'lock_dets'
In 7.3 you can get rid of these notices and/or tune the level of messages to STDOUT with the postgresql.conf options:
# # Message display # server_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error
There have recently been some growing pains by the PostgreSQL Global Development Group which have been reflected in the recent threads with the above heading. In the more than 150 messages a lot was discussed.
As PostgreSQL gains support in the marketplace, we as the PostgreSQL community must endure these growing pains and appreciate that they give us practical knowledge. Open Source is still an experiment. There are no rules or guidelines and what traditions open source groups have are based on what we do now and what we have done.
This was our first release with an advocacy group. There is room for improvement as there is always room for improvement in all areas.
It is important to notice that:
We have some sorting of priorities and tasks to do in all areas: websites, advocacy mailings, announcements, and the next release. We have a lot of new information about how things work (or don't) and we have a lot of coordination to do. And most importantly we have a devoted and growing users community to support.
This is cool. This is fun. Let's get back to it!
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009