pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Functions
3+ messages / 2 participants
[nested] [flat]
* SQL Functions
@ 2005-10-20 22:02 gurkan@resolution.com
0 siblings, 0 replies; 3+ messages in thread
From: gurkan@resolution.com @ 2005-10-20 22:02 UTC (permalink / raw)
To: pgsql-sql
I have been trying to find a way to return more than one but different types of
variables. How do I return more than one but mix types of variables.
Any help is appriaciated.
Thanks;
CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS ???? AS '
SELECT
DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived,
consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name,
companyDef.abbreviation, inv_accts.dateReceived, employee.firstname,
employee.lastname, inv_accts.type, contractDef.phase_id
FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices,
companyDef, acctDef, inv_contracts, contractDef, invention
WHERE consultant.id = employee.user_id
AND consultant.id = employee_offices.user_id
AND officeDef.id = employee_offices.office_id
AND invention.company_id = companyDef.id
AND inv_accts.inv_id = inv_contracts.inv_id
AND invention.id = inv_contracts.inv_id
AND inv_contracts.con_id = consultant.id
AND consultant.id = $1
AND invention.id = $2
--AND inv_accts.dateReceived BETWEEN $3 AND $4
AND inv_accts.acct_id = acctDef.id
AND acctDef.contract_id = inv_contracts.contract_id
AND inv_accts.type NOT LIKE ''DISCOVER%''
AND
(
(acctDef.description LIKE ''%PAYMENT%''
--AND acctDef.description NOT LIKE ''3\\%%''
AND strpos(acctDef.description, ''3%'') = 0
AND acctDef.description NOT LIKE ''%DROP SELL%''
)
OR inv_accts.type LIKE ''%BOUNCED CHECK%''
OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND''
)
AND contractDef.id = inv_contracts.contract_id
AND contractDef.id = acctDef.contract_id
ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived ASC;
' LANGUAGE SQL;
-------------------------------------------------
This mail sent through IMP: www.resolution.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* SQL Functions
@ 2005-10-21 14:19 gurkan@resolution.com
0 siblings, 1 reply; 3+ messages in thread
From: gurkan@resolution.com @ 2005-10-21 14:19 UTC (permalink / raw)
To: pgsql-sql
I have been trying to find a way to return more than one but different types of
variables. How do I return more than one but mix types of variables.
Any help is appriaciated.
Thanks;
CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS ???? AS '
SELECT
DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived,
consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name,
companyDef.abbreviation, inv_accts.dateReceived, employee.firstname,
employee.lastname, inv_accts.type, contractDef.phase_id
FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices,
companyDef, acctDef, inv_contracts, contractDef, invention
WHERE consultant.id = employee.user_id
AND consultant.id = employee_offices.user_id
AND officeDef.id = employee_offices.office_id
AND invention.company_id = companyDef.id
AND inv_accts.inv_id = inv_contracts.inv_id
AND invention.id = inv_contracts.inv_id
AND inv_contracts.con_id = consultant.id
AND consultant.id = $1
AND invention.id = $2
--AND inv_accts.dateReceived BETWEEN $3 AND $4
AND inv_accts.acct_id = acctDef.id
AND acctDef.contract_id = inv_contracts.contract_id
AND inv_accts.type NOT LIKE ''DISCOVER%''
AND
(
(acctDef.description LIKE ''%PAYMENT%''
--AND acctDef.description NOT LIKE ''3\\%%''
AND strpos(acctDef.description, ''3%'') = 0
AND acctDef.description NOT LIKE ''%DROP SELL%''
)
OR inv_accts.type LIKE ''%BOUNCED CHECK%''
OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND''
)
AND contractDef.id = inv_contracts.contract_id
AND contractDef.id = acctDef.contract_id
ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived
ASC;
' LANGUAGE SQL;
-------------------------------------------------
This mail sent through IMP: www.resolution.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL Functions
@ 2005-10-26 22:42 Thomas F. O'Connell <tfo@sitening.com>
parent: gurkan@resolution.com
0 siblings, 0 replies; 3+ messages in thread
From: Thomas F. O'Connell @ 2005-10-26 22:42 UTC (permalink / raw)
To: gurkan@resolution.com; +Cc: pgsql-sql
On Oct 21, 2005, at 9:19 AM, gurkan@resolution.com wrote:
> I have been trying to find a way to return more than one but
> different types of
> variables. How do I return more than one but mix types of variables.
> Any help is appriaciated.
> Thanks;
In PostgreSQL 8.1, you'll have output parameters available.
http://developer.postgresql.org/docs/postgres/plpgsql.html#PLPGSQL-
OVERVIEW
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Open Source Solutions. Optimized Web Development.
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-26 22:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-20 22:02 SQL Functions gurkan@resolution.com
2005-10-21 14:19 SQL Functions gurkan@resolution.com
2005-10-26 22:42 ` Thomas F. O'Connell <tfo@sitening.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox