[SQL] Default Permissions (repost from Novice)
Title: Default Permissions (repost from Novice) How can I set default permissions on tables, so that I don't have to manually set them for every table? p.s. This is a re-post from the novice list, I thought it might also be appropriate to ask here. MIKE WEAVER Software Developer 5, 42 Ladner Street O'Connor, WA, 6163 All correspondence: PO Box Y3502 East St Georges Terrace Perth WA 6832 P: (+618) 9331 2700 F: (+618) 9331 3733 M: 0419 299 170 W: http://www.corpusglobe.com/ E: [EMAIL PROTECTED] This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
FW: [SQL] Converting clarion
Title: FW: [SQL] Converting clarion -Original Message- From: Michael Weaver Sent: Friday, 31 January 2003 10:16 AM To: '[EMAIL PROTECTED]' Subject: RE: [SQL] Converting clarion If you have access to the Clarion database tools then it's really a pretty trivial task to export scripts to somthing more readable. If you don't have access to any of the tools that created your database, eg. you are coverting a client off a TPS based DB to a PostgreSQL DB, you've got a problem. TPS seems to be closly guarded file format. I have not been able to find any publicly avalible information or utils to help. -( other than there is not help. ) There are ODBC drivers for TPS files avalible (http://www.softvelocity.com/products/pr_database_tsodbc.htm) They cost somewhere in the order of $250USD There are a couple of applications that can extract data from the TPS files and export then as flatfiles, but these are quite hard to get hold of as they are part of the Clarion tool set. - Clarion programmers are your friends... Still, the ODBC Driver is probably your best bet as it allows 'no-mess no-fuss' dumping of your data directly into Postgres. ;) Mike Weaver. > -Original Message- > From: Nasair Junior da Silva [mailto:[EMAIL PROTECTED]] > Sent: Friday, 31 January 2003 4:37 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Converting clarion > > > Dear friends, > i'm looking for some program that converts clarion database > files to sql instructions. > > Someone can help-me ? > > thanks in advance. > > Nasair Jr. da Silva > Lajeado - RS - Brasil > > > xx===xx > || °v° Nasair Junior da Silva || > || /(_)\ Linux User: 246054 || > || ^ ^ [EMAIL PROTECTED] || > ||CPD - Desenvolvimento || > ||Univates - Centro Universitário|| > xx===xx > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] >
[SQL] "function has no parameter $1" - help.
Title: "function has no parameter $1" - help. I'm trying to create a trigger to enforce a constraint onto two possible foreign tables my function is defined as: <-- snip --> CREATE OR REPLACE FUNCTION sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, tbl_tranitem.fld_tranitem_type%TYPE) RETURNS OPAQUE AS ' DECLARE ---[Parameters]--- pl_product_id ALIAS FOR $1; pl_product_type ALIAS FOR $2; <-- snip --> but when I call the function I get the error: function has no parameter $1 What is going wrong? (I am using PostgreSQL 7.3.1) , Mike Weaver Software Developer 5, 42 Ladner Street O'Connor, WA, 6163 All correspondence: PO Box Y3502 East St Georges Terrace Perth WA 6832 P: (+618) 9331 2700 F: (+618) 9331 3733 M: 0403 385 181 W: http://www.corpusglobe.com/ E: [EMAIL PROTECTED] This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
[SQL] Passing arrays
Title: Passing arrays
How do you get an array of elements to be passed to a stored proc such that you can use the array in a SELECT statement in the WHERE clause
- e.g. WHERE field IN (array) etc...
CREATE OR REPLACE FUNCTION sp_report_retail_sales(int8[]) RETURNS SETOF sp_report_retail_sales_type AS '
DECLARE
---[Parameters]---
prod_id ALIAS FOR $1;
---[Variables]
retset record;
BEGIN
FOR retset IN
SELECT tbl_prdcrd.fld_prdcrd_id,
count (tbl_tranitem.fld_tranitem_productid) as num_sales,
sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity) as base_total,
sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity * tbl_tranitem.fld_tranitem_gst) as gst_total
FROM tbl_prdcrd INNER JOIN tbl_tranitem ON tbl_prdcrd.fld_prdcrd_id = tbl_tranitem.fld_tranitem_productid
INNER JOIN tbl_tran ON tbl_tranitem.fld_tranitem_transactionid = tbl_tran.fld_tran_id
WHERE tbl_prdcrd.fld_prdcrd_id IN (prod_id)
GROUP BY tbl_prdcrd.fld_prdcrd_id,
tbl_prdcrd.fld_prdcrd_type
LOOP
RETURN NEXT retset;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
We were calling the procedure as follows
select * from sp_report_retail_sales1 ('{1,3}');
We keep getting issues with casting.
WARNING: Error occurred while executing PL/pgSQL function sp_report_retail_sale
s_01
WARNING: line 8 at for over select rows
ERROR: Unable to identify an operator '=' for types 'bigint' and 'bigint[]'
You will have to retype this query using an explicit cast
Trying the parameter as text worked, but gave 0 rows in result set.
,
Mike Weaver
Software Developer
5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832
P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: [EMAIL PROTECTED]
This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
