[SQL] Installation woes
Title: Message Fundamentally if the user wants to run my software then I want to get it running on their hardware. If after they have tried it ( and hopefully liked it) they want to use it as a business tool then I am in a position to HIGHLY recommend the necessary upgrades to hardware/OS etc that they may need. Firstly there are no complaints about postgres or the installer both work very well. q1: I would like an almost silent install and to that effect have located the following on the installer project site: msiexec /i postgresql-8.0-beta2-dev3.msi datadir="C:\ghum\data" LISTENPORT=5432 INITDBOK=1 DOSERVICE=1 DOINITDB=1 SERVICEID="pgsql-80-beta-2" SERVICENAME="PostgreSQL Database Server 8.0 beta 2" SERVICEACCOUNT="postgres" SERVICEPASSWORD="PasswordUserPostgres" SERVICEPASSWORDV="PasswordUserPostgres" LOCALE="german" ENCODING="LATIN1" SUPERUSER="postgres" SUPERPASSWORD="leberwurst" SUPERPASSWORDV="leberwurst" I would like the installer to show a "You are installing PostgreSQL V 8 " banner with a pointer to the postgresql.org website but still bypass the normal installation questions.. q2: Is the above command in a format that is going to be supported in later versions? q3: Fat32 limitation. I have no evidence of the number of machines using FAT32 with W2000 / XP but I expect it will be significant. What I think I will have to do is put up a web page explaining the issues unless such a page already exists with the postgres documentation? q4: another option may be the cywin + postgres project. Does this let users access older operating systems W98 / Millenium edition? If so then is there a package my installer program can run? ie check OS/environment if ok run pg v8 else give warning / option and install cygwin+postgres. I don't mind if the install for W98 /fat32 is an older version of postgres. Indeed an older version would be helpful in emhasising the need to upgrade. I suppose posgresql is not "normally" a enduser software product. It has been designed as an industrial strength RDBMS for installation by professionals. In that environment it excells. I will lookforward to hearing how others are tackling the "embedded install". best regards Richard Sydney-Smith --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.772 / Virus Database: 519 - Release Date: 1/10/2004
Re: [SQL] Installation woes
a1: Not 100% sure waht you are looing for. Try adding "/qb" and see if that is what you meant :-) a2: That exact commandline may not be supported. But we intend to support commandline-driven silent installs, yes. a3: See the installer FAQ on http://pginstaller.projects.postgresql.org. a4: Yes, cygwin works on 9x platforms. And there is (will be, perhaps, I think there are currently a few issues with it) a version 8.0 for cygwin as well. //Magnus Fundamentally if the user wants to run my software then I want to get it running on their hardware. If after they have tried it ( and hopefully liked it) they want to use it as a business tool then I am in a position to HIGHLY recommend the necessary upgrades to hardware/OS etc that they may need. Firstly there are no complaints about postgres or the installer both work very well. q1: I would like an almost silent install and to that effect have located the following on the installer project site: msiexec /i postgresql-8.0-beta2-dev3.msi datadir="C:\ghum\data" LISTENPORT=5432 INITDBOK=1 DOSERVICE=1 DOINITDB=1 SERVICEID="pgsql-80-beta-2" SERVICENAME="PostgreSQL Database Server 8.0 beta 2" SERVICEACCOUNT="postgres" SERVICEPASSWORD="PasswordUserPostgres" SERVICEPASSWORDV="PasswordUserPostgres" LOCALE="german" ENCODING="LATIN1" SUPERUSER="postgres" SUPERPASSWORD="leberwurst" SUPERPASSWORDV="leberwurst" I would like the installer to show a "You are installing PostgreSQL V 8 " banner with a pointer to the postgresql.org website but still bypass the normal installation questions.. q2: Is the above command in a format that is going to be supported in later versions? q3: Fat32 limitation. I have no evidence of the number of machines using FAT32 with W2000 / XP but I expect it will be significant. What I think I will have to do is put up a web page explaining the issues unless such a page already exists with the postgres documentation? q4: another option may be the cywin + postgres project. Does this let users access older operating systems W98 / Millenium edition? If so then is there a package my installer program can run? ie check OS/environment if ok run pg v8 else give warning / option and install cygwin+postgres. I don't mind if the install for W98 /fat32 is an older version of postgres. Indeed an older version would be helpful in emhasising the need to upgrade. I suppose posgresql is not "normally" a enduser software product. It has been designed as an industrial strength RDBMS for installation by professionals. In that environment it excells. I will lookforward to hearing how others are tackling the "embedded install". best regards Richard Sydney-Smith ---(end of broadcast)--- TIP 3: 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
[SQL] bibliographic schema
hello we're looking for a SQL database schema for bibliographical references. the goal is to extract all the bibliographical references contained in our various existing pgsql scientific databases in only one specific database and to interconnect them with external keys and perl scripts. database schema of existing free projects (refdb, refbase, etc.) are not satisfactory. any link or suggestion will be welcome. (I hope this is the good list) thanks Guillaume ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Aggregate Function with Argument
I would like to create an aggregate function that returns a concatenation of grouped values. It would be particularly useful if I could pass an optional delimiter into the aggregate function. For example: With a table, 'team'... team_numbermember_name -- 1 David 1 Sanjay 1 Marie 2 Josh 2 Rani ... ...a query like: SELECT team_number, aggregated_concat(member_name, ', ' ) AS members FROM team GROUP BY team_number; ...would return: team_numbermembers --- 1 David, Sanjay, Marie 2 Josh, Rani ... Here's what I've got so far: /* For the default case, with no delimiter provided: */ CREATE FUNCTION concat(text, text) RETURNS text AS 'select $1 || $2;' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /* With a delimiter provided: */ CREATE FUNCTION concat(text, text, text) RETURNS text AS 'select $1 || $3 || $2;' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE AGGREGATE aggregated_concat ( sfunc = concat, basetype = text, stype = text ); My problem is I don't see how to make aggregated_concat accept an optional delimiter argument. Maybe it's not possible? Any ideas? Is there some completely different approach I should consider for concatenating grouped values? Thanks! David David Siegal Community Software Lab ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Aggregate Function with Argument
On Mon, Oct 18, 2004 at 11:24:33 -0400, David Siegal <[EMAIL PROTECTED]> wrote: > > My problem is I don't see how to make aggregated_concat accept an > optional delimiter argument. > Maybe it's not possible? > > Any ideas? > Is there some completely different approach I should consider for > concatenating grouped values? This exact same question has been asked previously and should be in the archives. My memory was that there currently isn't a way to pass the delimiter as an argument, but I don't remember whether or not any work arounds were suggested. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Ordering a record returned from a stored procedure
I am pulling a report from the database using a stored procedure but cannot get the information to return in a specific order unless I hardcode the order by clause. CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS setof submissionrec AS 'DECLARE result submissionrec%rowtype; hmhmkey ALIAS for $1; submissiondate ALIAS for $2; sort ALIAS for $3; BEGIN RAISE NOTICE ''The sort order should be: %.'', sort; FOR result IN SELECT (. select all necessary fields ...) FROM ( tables ...) WHERE (... contraints) ORDER BY sortLOOP RETURN next result;END LOOP; RETURN result; END;' LANGUAGE plpgsql; What am I missing? The returned data is ordered if the "Order By" clause has the values hard coded but doesn't seem to read the "sort" variable. Any help would be appreciated. Kent Anderson
Re: [SQL] Ordering a record returned from a stored procedure
On Mon, 18 Oct 2004, Kent Anderson wrote: > I am pulling a report from the database using a stored procedure but cannot > get the information to return in a specific order unless I hardcode the > order by clause. > > CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS > setof submissionrec AS ' > DECLARE > result submissionrec%rowtype; > hmhmkey ALIAS for $1; > submissiondate ALIAS for $2; > sort ALIAS for $3; > > BEGIN > RAISE NOTICE ''The sort order should be: %.'', sort; > FOR result IN > SELECT > (. select all necessary fields ...) > FROM > ( tables ...) > WHERE > (... contraints) > > ORDER BY sort > LOOP > RETURN next result; > END LOOP; > > RETURN result; > > > END; > ' LANGUAGE plpgsql; > > What am I missing? The returned data is ordered if the "Order By" clause has > the values hard coded but doesn't seem to read the "sort" variable. You're telling it to order by the value of the third argument, not the value of the column with the name of the third argument. I think right now you'd need to use EXECUTE to put it in as if it were the expression to sort on. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] problems using phpPgAmin
Hi, to those of you that may be using the above named admin tool, any ideas why I am unable to login with the postgres username even though I have amended the pg_hb file? Are there any other admin tools out there that i could use on a a unix box? many thanks Andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Database backup
sreejith s wrote: How to take Database backup from an application developed in Visual Basic thats running at Windows Client and Database resting at Linux Server. THanx You wrote a similar question some days ago and mentioned that pg_dump wouldn't work on Windows, so I figure you don't want to let the Linux host computer make the backups but your Windows client. You could go 2 ways. 1) Install pg_dump on your Windows client. Either as PostgreSQL on a CygWin environment or a Windows-native PostgreSQL 7.5 or 8.0 installation. Since you only need pg_dump you wouldn't configure a running PostgreSQL so this should be pretty easy. E.g. the Linux host's IP is 192.168.0.123. Then you can run on Windows : pg_dump --host=192.168.0.123 -U your_username your_database > your_database.sql 2) Let the Linux server do the backup and transfer the resulting backup file as soon as possible to your Windows client. a) The server could push it through SAMBA, SCP or FTP. b) The client could collect all backup files when it gets booted, if the server puts the files in a SAMBA share that the client can mount as network drive or access via FTP. I'd propose you take the alternative 2) b). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] problems using phpPgAmin
I really like pgadmin3: http://www.pgadmin.org Rick beyaNet <[EMAIL PROTECTED]>To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: [SQL] problems using phpPgAmin tgresql.org 10/18/2004 11:28 AM Hi, to those of you that may be using the above named admin tool, any ideas why I am unable to login with the postgres username even though I have amended the pg_hb file? Are there any other admin tools out there that i could use on a a unix box? many thanks Andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Ordering a record returned from a stored procedure - date issue
I have the code working except for the date part of the where clause. Can anyone point out how yield_date = 10/18/2004 can be translated so the 10/18/2004 is coming from a variable? ie yield_date = '' ... variable with date Thanks This works but the date is hardcoded. FOR result IN EXECUTE ''SELECT (. select all necessary fields ...) FROM ( tables ...) WHERE hm_key= '' || hmhmkey || '' AND yield_date = 10/18/2004 ORDER BY '' || sort LOOP RETURN next result; END LOOP; RETURN result; This doesn't work and I am sure its due to all the getting out of hand when I try to have a date variable used by the string. (sorry for the mess of apostraphes) I have tried several variations but keep getting no records returned or an error. yield_date = '' || || submissiondate || || '' (returns 0 rows but no error - the date variable does have a valid date in it) yield_date = || submissiondate || (returns ERROR: invalid input syntax for type date: " || submissiondate || ") yield_date = '' || submissiondate || '' (returns ERROR: syntax error at or near "$2" at character 982) yield_date = '' || '' || submissiondate || '' || '' (returns ERROR: column "submissiondate" does not exist) FOR result IN EXECUTE ''SELECT (. select all necessary fields ...) FROM ( tables ...) WHERE hm_key= '' || hmhmkey || '' AND yield_date = '' || || submissiondate || || '' ORDER BY '' || sort LOOP RETURN next result; END LOOP; RETURN result; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo Sent: Monday, October 18, 2004 11:25 AM To: Kent Anderson Cc: [EMAIL PROTECTED] Org Subject: Re: [SQL] Ordering a record returned from a stored procedure On Mon, 18 Oct 2004, Kent Anderson wrote: > I am pulling a report from the database using a stored procedure but cannot > get the information to return in a specific order unless I hardcode the > order by clause. > > CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS > setof submissionrec AS ' > DECLARE > result submissionrec%rowtype; > hmhmkey ALIAS for $1; > submissiondate ALIAS for $2; > sort ALIAS for $3; > > BEGIN > RAISE NOTICE ''The sort order should be: %.'', sort; > FOR result IN > SELECT > (. select all necessary fields ...) > FROM > ( tables ...) > WHERE > (... contraints) > > ORDER BY sort > LOOP > RETURN next result; > END LOOP; > > RETURN result; > > > END; > ' LANGUAGE plpgsql; > > What am I missing? The returned data is ordered if the "Order By" clause has > the values hard coded but doesn't seem to read the "sort" variable. You're telling it to order by the value of the third argument, not the value of the column with the name of the third argument. I think right now you'd need to use EXECUTE to put it in as if it were the expression to sort on. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Ordering a record returned from a stored procedure - date issue
Never mind, it requires on each side of the variable. Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kent Anderson Sent: Monday, October 18, 2004 1:32 PM To: [EMAIL PROTECTED] Org Subject: Re: [SQL] Ordering a record returned from a stored procedure - date issue I have the code working except for the date part of the where clause. Can anyone point out how yield_date = 10/18/2004 can be translated so the 10/18/2004 is coming from a variable? ie yield_date = '' ... variable with date Thanks This works but the date is hardcoded. FOR result IN EXECUTE ''SELECT (. select all necessary fields ...) FROM ( tables ...) WHERE hm_key= '' || hmhmkey || '' AND yield_date = 10/18/2004 ORDER BY '' || sort LOOP RETURN next result; END LOOP; RETURN result; This doesn't work and I am sure its due to all the getting out of hand when I try to have a date variable used by the string. (sorry for the mess of apostraphes) I have tried several variations but keep getting no records returned or an error. yield_date = '' || || submissiondate || || '' (returns 0 rows but no error - the date variable does have a valid date in it) yield_date = || submissiondate || (returns ERROR: invalid input syntax for type date: " || submissiondate || ") yield_date = '' || submissiondate || '' (returns ERROR: syntax error at or near "$2" at character 982) yield_date = '' || '' || submissiondate || '' || '' (returns ERROR: column "submissiondate" does not exist) FOR result IN EXECUTE ''SELECT (. select all necessary fields ...) FROM ( tables ...) WHERE hm_key= '' || hmhmkey || '' AND yield_date = '' || || submissiondate || || '' ORDER BY '' || sort LOOP RETURN next result; END LOOP; RETURN result; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo Sent: Monday, October 18, 2004 11:25 AM To: Kent Anderson Cc: [EMAIL PROTECTED] Org Subject: Re: [SQL] Ordering a record returned from a stored procedure On Mon, 18 Oct 2004, Kent Anderson wrote: > I am pulling a report from the database using a stored procedure but cannot > get the information to return in a specific order unless I hardcode the > order by clause. > > CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS > setof submissionrec AS ' > DECLARE > result submissionrec%rowtype; > hmhmkey ALIAS for $1; > submissiondate ALIAS for $2; > sort ALIAS for $3; > > BEGIN > RAISE NOTICE ''The sort order should be: %.'', sort; > FOR result IN > SELECT > (. select all necessary fields ...) > FROM > ( tables ...) > WHERE > (... contraints) > > ORDER BY sort > LOOP > RETURN next result; > END LOOP; > > RETURN result; > > > END; > ' LANGUAGE plpgsql; > > What am I missing? The returned data is ordered if the "Order By" clause has > the values hard coded but doesn't seem to read the "sort" variable. You're telling it to order by the value of the third argument, not the value of the column with the name of the third argument. I think right now you'd need to use EXECUTE to put it in as if it were the expression to sort on. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] problems using phpPgAmin
On Mon, 2004-10-18 at 17:28, beyaNet wrote: > Hi, > to those of you that may be using the above named admin tool, any ideas > why I am unable to login with the postgres username even though I have > amended the pg_hb file? How have you amended pg_hba.conf? did you send SIGHUP to the postmaster afterwards to get it to reload the settings? > Are there any other admin tools out there that > i could use on a a unix box? pgadmin3 -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Delight thyself also in the LORD; and he shall give thee the desires of thine heart." Psalms 37:4 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] bibliographic schema
Guillaume, > we're looking for a SQL database schema for bibliographical references. > the goal is to extract all the bibliographical references contained in > our various existing pgsql scientific databases in only one specific > database and to interconnect them with external keys and perl scripts. I'm afraid that bibliographic references is a rather esoteric need in the OSS community.I personally haven't seen such a thing. You may have to create your own. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
