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.

Contributors: Craig Sturman craig at eliquid.com, Darren Ferguson darren at crystalballinc.com, Stephan Szabo sszabo at megazone23.bigpanda.com

Interbase function translated to a Postgresql function [GENERAL] example of Create Function 21-Oct-2002

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)

Contributors: Roberto (SmartBit) roberto at smartbit.inf.br, Joe Conway mail at joeconway.com

CASE Statement logic [GENERAL] executing delete in a case statement? 05-Nov-2002

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);

Contributors: Jean-Christian Imbeault jc at mega-bucks.co.jp, Richard Huxton dev at archonet.com, Stephan Szabo sszabo at megazone23.bigpanda.com, Christoph Dalitz christoph.dalitz at hs-niederrhein.de

String Aggregate Example [GENERAL] table linking problem 04-Oct-2002

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;

Contributors: jacky at xtrapower.net, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Richard Huxton dev at archonet.com, Holger Klawitter lists at klawitter.de

Notice and other log messages [GENERAL] Turning off information messages 04-Nov-2002

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

Contributors: Mike Howard aixguru at btopenworld.com, Bruce Momjian pgman at candle.pha.pa.us

Editorial [HACKERS] [GENERAL] PostgreSQL Global Development Group 14-Dec-2002

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.

• The release announcement didn't make it to many core people until after the fact. This caused some of the content on the main site to become out of date.
• The content and format of the release announcement was argued both for and against.
• The announced download link pointed only to advocacy and didn't at first use the mirror sites.
• There was a blurry distinction between the advocacy group and the web site group and a mention of fiefdoms.
• There were some ordinary tech vs. marketing wars:
  • The difficulty in marketing a open source database vs. commercial databases,
  • Having a tech release announcement vs. a press release announcement,
  • And dragging in MySQL yet again as an example of what to do and what not to do.
• Assorted minor flames flew.
• (I'm sure I missed someone's favorite grouch, um, point and will hear about.)
• And there were a lot of really good ideas.

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 a common goal of supporting and promoting PostgreSQL.
• Anyone and everyone can speak up.
• The work is more important than the players.
• Minor squabbles are unavoidable and unimportant.
• The work everyone does is hard but is backed with equal enthusiasm.
• We never stop improving the product, the support and the promotion.

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!

Editor: elein at varlena.com