[SQL] Function returning an array
I want a function to return an array of two VARCHAR(255). This works, but does not return VARCHAR(255): CREATE FUNCTION ins_div(CHAR(8)) RETURNS _varchar AS '...' LANGUAGE 'plpgsql'; These don't work: CREATE FUNCTION ins_div(CHAR(8)) RETURNS _varchar(255) AS '...' LANGUAGE 'plpgsql'; CREATE FUNCTION ins_div(CHAR(8)) RETURNS varchar(255)[2] AS '...' LANGUAGE 'plpgsql'; CREATE FUNCTION ins_div(CHAR(8)) RETURNS varchar[2](255) AS '...' LANGUAGE 'plpgsql'; CREATE FUNCTION ins_div(CHAR(8)) RETURNS setof varchar(255) AS '..' LANGUAGE 'plpgsql'; What's the correct syntax to return two VARCHAR(255)? Francesco Casadei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Function returning an array
Ciao Francesco: > What's the correct syntax to return two VARCHAR(255)? There isn't one. At this time, functions cannot return arrays. Look for a fix to this limitation with version 7.2 or later. Jan Wieck and others have been working to extend functions so that they may manipulate and return cursors. However, that functionality is still in development and I don't know whether it will be included in the next release. In the meantime, I suggest that you take one of the following courses: 1. Delimit or tokenize your function output and parse it on the receiving end into 2 fields (e.g. 'element 1|element2') 2. Do the operation in your middleware, where you can use C, Java, Python or similar to pass arrays. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function returning an array
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> What's the correct syntax to return two VARCHAR(255)? > There isn't one. At this time, functions cannot return arrays. Well, they *can*, it's just that there's no syntax to support constructing an array value in SQL or plpgsql languages. I know that you can do it in pltcl (there are examples in the pltcl self-test), and of course you can do it in C. A brute-force solution is to make a support function in one of those languages that takes two varchars and returns an array of varchar. regards, tom lane ---(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] Execute permsissions on fuctions
Other SQL servers have the concept of stored procedures having different permissions. For instance a procedure that can update a table. Since a web site typically connects as the webuser (or equiv postgres user), I do not want to offer update to the webuser. The way I have done this elsewhere is to create a stored procedure that could update the table, and allow the webuser to update the table. The procedure had perms of a user who could update the table, but the webuser could not. How can I do this in Postgres? Thanks. -- Zot O'Connor http://www.ZotConsulting.com http://www.WhiteKnightHackers.com ---(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
Re: [SQL] Function returning an array
On Thu, 23 Aug 2001, Tom Lane wrote: > "Josh Berkus" <[EMAIL PROTECTED]> writes: > >> What's the correct syntax to return two VARCHAR(255)? > > > There isn't one. At this time, functions cannot return arrays. > > Well, they *can*, it's just that there's no syntax to support > constructing an array value in SQL or plpgsql languages. Most importantly, there's no syntax to support deconstructing an array ;) I have patches to support "select * from function(args)" almost done, I just need to perform merge against -current. But unfortunately RL took most of my time and I was unable to work on pg-related things for last months. The patches contain quite a lot of changes to core structures (RangeTblEntry and others) and I want to get them in before 7.2 gets frozen. Hopefully this weekend I'll have some time to do merge and cleanup Also included in the patch is ability to do 'select * from cursor foo'. -alex ---(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] Re: [GENERAL] protected ON DELETE CASCADE
Murray Hobbs wrote: > > here's my problem > > i have tables A, B, C, D > > A <- B > A <- C <- D > > i want to maintain integrity so that if A is deleted from then so is > anything referencing from B and C - no problem ON DELETE CASCADE > > but if there are any D's that point back to A (through composite key in > C) i don't want the delete to go ahead - at all - i want an error > message and condition So B and C reference A with ON DELETE CASCADE, while D references C without it. The default behaviour of a foreign key constraint is ON DELETE NO ACTION, which confusingly enough aborts the transaction (it's defined that way in the SQL standard, don't ask me why they called it NO ACTION). Thus a deletion from A will cascaded delete from C, but then the constraint on D will abort the transaction if this automatic delete from C would orphan a reference from D. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Re: [GENERAL] protected ON DELETE CASCADE
i neglected to show it properly have tables A, B, C, D PLUS a few others A <- B F | v A <- C <- D ^ | E i want to delete from C and cascade any delete to E or F but not if there are records in D what i have done is to have ON DELETE CASCADE on C's primary but force deletes to C through a function that will delete from C only if there is no records in D but i would like to believe there is a better way - a way that does not require that i do all my deletes through a function cheers murray Oliver Elphick wrote: > > Murray Hobbs wrote: > > > >here's my problem > > > >i have tables A, B, C, D > > > >A <- B > >A <- C <- D > > > >i want to maintain integrity so that if A is deleted from then so is > >anything referencing from B and C - no problem ON DELETE CASCADE > > > >but if there are any D's that point back to A (through composite key in > >C) i don't want the delete to go ahead - at all - i want an error > >message and condition > > If the reference from D to C uses ON DELETE RESTRICT (or NO ACTION), that > should fail and thus cause the original DELETE to fail. > > -- > Oliver Elphick[EMAIL PROTECTED] > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > > "For God hath not appointed us to wrath, but to obtain > salvation by our Lord Jesus Christ, Who died for us, > that, whether we wake or sleep, we should live > together with him." > I Thessalonians 5:9,10 Jan Wieck wrote: > > Murray Hobbs wrote: > > > > here's my problem > > > > i have tables A, B, C, D > > > > A <- B > > A <- C <- D > > > > i want to maintain integrity so that if A is deleted from then so is > > anything referencing from B and C - no problem ON DELETE CASCADE > > > > but if there are any D's that point back to A (through composite key in > > C) i don't want the delete to go ahead - at all - i want an error > > message and condition > > So B and C reference A with ON DELETE CASCADE, while D > references C without it. The default behaviour of a foreign > key constraint is ON DELETE NO ACTION, which confusingly > enough aborts the transaction (it's defined that way in the > SQL standard, don't ask me why they called it NO ACTION). > Thus a deletion from A will cascaded delete from C, but then > the constraint on D will abort the transaction if this > automatic delete from C would orphan a reference from D. > > Jan > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [GENERAL] protected ON DELETE CASCADE
Murray Hobbs wrote: > > i neglected to show it properly > > have tables A, B, C, D PLUS a few others > > A <- B > > F > | > v > A <- C <- D > ^ > | > E > > i want to delete from C and cascade any delete to E or F but not if > there are records in D > > what i have done is to have ON DELETE CASCADE on C's primary How? You cannot specify the ON DELETE behaviour on the primary key. You specify it on the foreign key definition, and there's no reason why these definitions may not be different between D, E and F. > > but force deletes to C through a function that will delete from C only > if there is no records in D Exactly that is the JOB of a foreign key constraint, or do you want to silently suppress the delete from C instead of bailing out with a transaction abort? > > but i would like to believe there is a better way - a way that does not > require that i do all my deletes through a function Why doesn't this work for you? CREATE TABLE A ( aa integer, PRIMARY KEY (aa) ); CREATE TABLE C ( ca integer, cc integer, PRIMARY KEY (ca, cc), FOREIGN KEY (ca) REFERENCES A (aa) ON DELETE CASCADE ); CREATE TABLE D ( da integer, dc integer, FOREIGN KEY (da, dc) REFERENCES C (ca, cc) ); CREATE TABLE E ( ea integer, ec integer, FOREIGN KEY (ea, ec) REFERENCES C (ca, cc) ON DELETE CASCADE ); CREATE TABLE F ( fa integer, fc integer, FOREIGN KEY (fa, fc) REFERENCES C (ca, cc) ON DELETE CASCADE ); With this setup, you will not be able to delete any data from A or C that is referenced from D. Anything else is deletable and will cause referencing rows from C, E and F to go away as well. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: [GENERAL] protected ON DELETE CASCADE
cool, thanks yes, i was slack reading th docs m Jan Wieck wrote: > > Murray Hobbs wrote: > > > > i neglected to show it properly > > > > have tables A, B, C, D PLUS a few others > > > > A <- B > > > > F > > | > > v > > A <- C <- D > > ^ > > | > > E > > > > i want to delete from C and cascade any delete to E or F but not if > > there are records in D > > > > what i have done is to have ON DELETE CASCADE on C's primary > > How? You cannot specify the ON DELETE behaviour on the > primary key. You specify it on the foreign key definition, > and there's no reason why these definitions may not be > different between D, E and F. > > > > > but force deletes to C through a function that will delete from C only > > if there is no records in D > > Exactly that is the JOB of a foreign key constraint, or do > you want to silently suppress the delete from C instead of > bailing out with a transaction abort? > > > > > but i would like to believe there is a better way - a way that does not > > require that i do all my deletes through a function > > Why doesn't this work for you? > > CREATE TABLE A ( > aa integer, > > PRIMARY KEY (aa) > ); > > CREATE TABLE C ( > ca integer, > cc integer, > > PRIMARY KEY (ca, cc), > FOREIGN KEY (ca) REFERENCES A (aa) ON DELETE CASCADE > ); > > CREATE TABLE D ( > da integer, > dc integer, > > FOREIGN KEY (da, dc) REFERENCES C (ca, cc) > ); > > CREATE TABLE E ( > ea integer, > ec integer, > > FOREIGN KEY (ea, ec) REFERENCES C (ca, cc) ON DELETE CASCADE > ); > > CREATE TABLE F ( > fa integer, > fc integer, > > FOREIGN KEY (fa, fc) REFERENCES C (ca, cc) ON DELETE CASCADE > ); > > With this setup, you will not be able to delete any data from > A or C that is referenced from D. Anything else is deletable > and will cause referencing rows from C, E and F to go away as > well. > > Jan > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
