[SQL] Stored Procedures returning a RECORD
I am attempting to use a stored procedure to pull a report from the database. My questions is if its even possible to pull the data using a function and then treat the returned data as a normal recordset with the web pages. The actual function looks like CREATE OR REPLACE FUNCTION submissionreport(integer, date, text) RETURNS RECORD AS' DECLARE somekey ALIAS for $1; somedate ALIAS for $2; somesortorder ALIAS for $3; submission RECORD; BEGIN SELECT INTO submission (... stuff goes here)ORDER BY sort; RETURN submission;END;' LANGUAGE plpgsql; The error being returned is in the web pages" SQLState: 08S01 Native Error Code: 1 Error while executing the query; ERROR: cannot display a value of type record" Kent Anderson EZYield.com 407-629-0900 www.ezyield.com This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
[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 - 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]
[SQL] Finding duplicated values
I have a few tables that have duplicated values from an import from a different database. I have two keys I tried to set as primary and got an error ERROR: could not create unique indexDETAIL: Table contains duplicated values. Is there some join I can use to compare the hmhmkey, wmwmkey pairs against the table to find duplicate values? Each pair key should be unique but the old database was less than normalized. I was trying to use the code below but it returned no rows. SELECT hmhmkey, wmwmkey FROM exceptionsEXCEPTSELECT hmhmkey, wmwmkey FROM exceptions; Any suggestions? Kent Anderson EZYield.com 407-629-0900 www.ezyield.com This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
[SQL] How do you compare contents of two tables using 2 pk
I have two tables that should contain the same number or records. Both tables use the same 2 foreign keys as their primary keys. I did a count on the number of records in both and found one table has 500 records less than the first table so I need to know which records are not in the second table. table 1 t1_fk_wm (FK) t1_fk_hm (FK) ... ... ... table 2 t2_fk_wm (FK) t2_fk_hm (FK) Table 2 doesn't have the same number of records as the first table however it should. Any suggestions would be appreciated as I am still very much a beginner at SQL. Kent Anderson
