Re: [GENERAL] help me please with function
I can not find command in postgres - With ... as You need Postgres 8.4 for that: http://www.postgresql.org/docs/current/static/queries-with.html Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] help me please with function
Help me please with this function writed in MSSQL. I want to pass on Postgres CREATE OR REPLACE FUNCTION GetProductsByCategoryId (IN CategoryId int, IN PageIndex int, IN NumRows int, OUT CategoryName varchar(50), OUT CategoryProductCount int) RETURNS SETOF RECORD LANGUAGE plpgsql as $$ DECLARE startRowIndex int; BEGIN /* The below statements enable returning the Total Product Count and friendly Name for the CategoryId as output paramsters to our SPROC. This enables us to avoid having to make a separate call to the database to retrieve them, and can help improve performance quite a bit */ CategoryProductCount = (SELECT COUNT(*) FROM Products where Products.CategoryId = CategoryId) CategoryName = (SELECT Name FROM Categories Where Categories.CategoryID = CategoryId) /* The below statements use the new ROW_NUMBER() function in SQL 2005 to return only the specified rows we want to retrieve from the Products table */ --Declare @startRowIndex INT; --set @startRowIndex = (@PageIndex * @NumRows) + 1; startRowIndex := (PageIndex * NumRows) + 1; With ProductEntries as ( (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost FROM Products WHERE CategoryId=CategoryId) ) (Select ProductId, CategoryId, Description, ProductImage, UnitCost FROM ProductEntries WHERE Row between startRowIndex and StartRowIndex+NumRows-1) END; $$; I have a problem whis instraction With ProductEntries as ( (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost FROM Products WHERE CategoryId=CategoryId) ) I can not find command in postgres - With ... as My changed function is CREATE OR REPLACE FUNCTION "public"."getproductsbycategoryid_refcursor" (categoryid integer, pageindex integer, numrows integer, out product_id integer, out category_id integer, out description varchar, out product_image varchar, out unit_cost varchar) RETURNS SETOF record AS $body$ DECLARE startrowindex int; categoryproductcount int; categoryname varchar(50); BEGIN SELECT COUNT(*) INTO categoryproductcount FROM products where products."category_id"=categoryid; SELECT "name" INTO categoryname FROM categories Where categories."category_id"=categoryid; startrowindex = (pageindex * numrows) + 1; -- ERROR HERE With productentries as SELECT ROW_NUMBER() OVER (ORDER BY products."category_id" ASC) as Row, products."product_id", products."category_id", products."description", products."product_image", products."unit_cost" FROM products WHERE products."category_id"=categoryid; RETURN QUERY SELECT productentries."product_id", productentries."category_id", productentries."description", productentries."product_image", productentries."unit_cost" FROM productentries WHERE productentries."row" between startrowindex and startrowindex+numrows-1; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000; Help me please, excuse me for bad inglish ;)
Re: [GENERAL] Help me please...
ja...@aers.ca wrote: if you used default locations I believe it should be (this is from memory mind) under c:\program files\postgres\\data\ data is the folder you want. First, verify the location of pgdata... sc qc pgsql-8.3 (I'm assuming this is 8.3, modify for other versions) note the value of the -D parameter on the BINARY_PATH_NAME, like... BINARY_PATH_NAME : D:\postgres\8.3\bin\pg_ctl.exe runservice -w -N "pgsql-8.3" -D "D:\postgres\8.3\data\" hence, mine is D:\postgres\8.3\data\ If you've reinstalled postgres from scratch, you'll likely have to do a bit of dinking around. First, NET STOP pgsql-8.3 Now, MOVE the current data dir somewhere safe, and COPY/S your backup of the DATA directory to the active location. Then, use the 'security' dialog in file explorer, or the CACL command line, to grant the 'postgres' user full control over the data directory and all files in it. command line version: cacls /t /e /c /g postgres:f \path\to\data if this postgres user already existed from before, but the reinstalled postgres service is having problems starting, you may need to reset the service account password.pick a random forgettable password. I'm going to use shattUp373treHodhu (random generator output)... NET USER postgres shattUp373treHodhu SC CONFIG pgsql-8.3 password= shattUp373treHodhu upper case doesn't matter in the commands except for the password itself but the spacing around the password= is critical (none before the =, one space after) then try net start pgsql-8.3 and with any luck, your data is all intact. its absolutely critical if you've reinstalled postgres that you install the same version as you used before. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me please...
if you used default locations I believe it should be (this is from memory mind) under c:\program files\postgres\\data\ data is the folder you want. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ja...@aers.ca Sent: Thursday, July 09, 2009 1:18 PM To: don2_...@yahoo.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] Help me please... do the DB folders still exist? if so back them up, reinstall Postgres (reinstalling XP probably wiped out either DLL's or registry entries) and relaunch it. don't have it initialize a DB on install From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Roseller A. Romanos Sent: Wednesday, July 08, 2009 9:37 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Help me please... Please help me with this. I really need your advice as to how to retrieve the data in my postgresql database. I have postgresql installed in Windows XP platform five months ago. Just yesterday my OS bugged down and saying NTDLR is missing. What I did was I re-installed my OS. When I finished my installation I found out that I cannot access anymore my postgresql using PGAdmin III. What should I do to access my database and retrieve the important records in it? Unfortunately, I have not created a backup of it. Please help me. Thanks in advance and God bless... Roseller Romanos STI-Pagadian Gillamac Building, Pagadian City Office Nos: (062) 2144-785 Home Nos: (062) 2144-695 Mobile Nos: 09203502636
Re: [GENERAL] Help me please...
do the DB folders still exist? if so back them up, reinstall Postgres (reinstalling XP probably wiped out either DLL's or registry entries) and relaunch it. don't have it initialize a DB on install From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Roseller A. Romanos Sent: Wednesday, July 08, 2009 9:37 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Help me please... Please help me with this. I really need your advice as to how to retrieve the data in my postgresql database. I have postgresql installed in Windows XP platform five months ago. Just yesterday my OS bugged down and saying NTDLR is missing. What I did was I re-installed my OS. When I finished my installation I found out that I cannot access anymore my postgresql using PGAdmin III. What should I do to access my database and retrieve the important records in it? Unfortunately, I have not created a backup of it. Please help me. Thanks in advance and God bless... Roseller Romanos STI-Pagadian Gillamac Building, Pagadian City Office Nos: (062) 2144-785 Home Nos: (062) 2144-695 Mobile Nos: 09203502636
[GENERAL] Help me please...
Please help me with this. I really need your advice as to how to retrieve the data in my postgresql database. I have postgresql installed in Windows XP platform five months ago. Just yesterday my OS bugged down and saying NTDLR is missing. What I did was I re-installed my OS. When I finished my installation I found out that I cannot access anymore my postgresql using PGAdmin III. What should I do to access my database and retrieve the important records in it? Unfortunately, I have not created a backup of it. Please help me. Thanks in advance and God bless... Roseller Romanos STI-Pagadian Gillamac Building, Pagadian City Office Nos: (062) 2144-785 Home Nos: (062) 2144-695 Mobile Nos: 09203502636
Re: [GENERAL] Help me please!! -newbie-
"[Bad-Knees]" <[EMAIL PROTECTED]> writes: > Anyone got a solution for this error: > initdb: pg_encoding failed Is your PATH pointing at the right set of Postgres executables? I think that would happen if initdb finds a 'postgres' in PATH that doesn't have pg_encoding along with it, which would happen if that version weren't compiled with multibyte support... regards, tom lane
[GENERAL] Help me please!! -newbie-
Anyone got a solution for this error: initdb: pg_encoding failed Perhaps you did not configure PostgreSQL for multibyte support or the program was not successfully installed. --- Here's what i wrote: --- ./configure --prefix=/usr/local/postgresql --enable-locale\ --enable-multibyte --with-odbc --with-tcl make make install -- Everything should be ok, so.. HELP!! ..if it aint broke, tweak it.
