Re: [SQL] [HACKERS] Schema boggle...
Hi Marc, I was actually leaning towards schema's as a method to partition the data out when I was beginning to plan AMS 2, your suggestions of schema's confirmed this as a good approach for what we were looking for so I started to code the initial layout for the Schema in April/May, but at the time I did not have the full picture in my head on dealing with some sections of the database To address Josh's comments though. Monolithic tables have their uses, I don't and wont contest that, however we've been using a monolithic structure in our current AMS distribution and in a lot of areas it has been quite a pain to try and bring some uniformity into the structure. First off, I have an ever changing platform and one of the issues I had was that development under the monolithic structure because problematic and very difficult to merge new elements in, granted the SQL schema was not all that great and a number of areas could have used improvement, but over all it was more trouble then it was worth. By breaking out our client content into a schema structure, I've simplified the management of my data immensely, I can easily delete old/unused data with out having to worry about updating scripts that have been written to clean out structures. I can give clients greater control over their own data with out worrying about some "security" issue that I might have missed in my programming, so there's and added level of safety in an already tight structure. I've "objectified" out the content into an easily changeable/"update friendly" system of tables, views and functions. I've reduced a fair amount of overhead by limiting the order of tables, For instance, with our original monolithic dataset, we have approx 90+ tables handling all of the content that we use on a regular basis. With this new structure I've reduced that down to a total of 30 tables, 8 of which are repeated across the schemas. The method we are using the schemas in also allows me to work in some assumptions that were tricky to code under the monolithic structure, with the schema system, simplicity of the table set allowed allowed me to simulate those assumptions with out having to re-write code each time I have an update, thus giving me a highly dynamic dataset. That and I'm more confident working on a 3D level, versus 2D, the schema levels introduce the third dimension into my structure and simplify visualizing how I want things to work. Within that third dimensional structure, it made sense to be able to code out an SQL statement that would have searched the schema set for matching patterns to that given in the SQL query, similar to the way one can search for a pattern on data in a column. But Tom has given me an idea that will allow me to work out how to search multiple schemas within a dynamic plpgsql function that figures out all my patterned schemas and executes the resulting query as he suggested, I just need to learn plpgsql programming so that I can work out all of the details (Tom, I might bug you about that once in a while). At 09:45 PM 11/5/03 -0400, Marc G. Fournier wrote: Actually, the use of schema's was my idea, to speed up some dreadfully slow queries dealing with traffic stats from a table that was growing painfully monolithic ... the idea is/was that it would be easier to backup/remove all data pertaining to a specific client if they decided to close their account ... On Wed, 5 Nov 2003, Josh Berkus wrote: > Chris, > > > I work with Marc Fournier, for those who don't know, and have been > > working extensively with the schemas feature for Hub.Org's new Account > > Management package. Each client's data is stored in a seperate schema > > set asside just for them (though they will never have direct access to > > it, it helps us "balance" out the data. Each schema has the same set of > > tables, with which different data is stored, depending on the client. > > While this is an interesting use of Schema, it is not, IMHO, a good way to > partition client data.Far better to have monolithic tables with a > "client_id" column and then construct *views* which are available in each > schema only to that client. Otherwise, as you've discovered, top-level > management becomes a royal pain. > > I'd be happy to discuss this further on PGSQL-SQL, which is really the > appropriate mailing list. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(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] Problem with plpgsql function
Hi All,
I've been bangin away on a 7.4.x based database server trying to get a
plpgsql function to work the way I'm expecting it to. I've used various
resourced on the net to come up with the function, and as far as I can
tell it's "in proper form", but I've got the feeling that I'm missing
something.
I've created a new data type called:
CREATE TYPE account_info AS (username text, password text);
With that I want to return multiple rows, based on the results of the
function, using the SETOF and rowtype declarations, such that the
function looks like:
CREATE OR REPLACE FUNCTION get_account_info(text) RETURNS SETOF
account_info AS '
DECLARE
acc account_info%rowtype;
domain_name ALIAS FOR $1;
company_id RECORD;
BEGIN
acc.username := NULL;
acc.password := NULL;
SELECT INTO company_id cs.id, to_char(cs.id, ''FM0999'') AS cid FROM
virtual_host vh
LEFT JOIN virtual_machine vm ON (vm.id = vh.vm_id)
LEFT JOIN company_summary cs ON (cs.id = vm.company_id)
WHERE vh.domain_name = domain_name;
FOR acc IN EXECUTE ''SELECT '''' || company_id.cid || '''' || c.id,
a.password FROM company_summary cs
LEFT JOIN contact c ON (c.company_id =
cs.id)
LEFT JOIN company_'' || company_id.cid ||
''.account a ON (a.contact_id = c.id)
WHERE cs.id = '' || company_id.id
LOOP
RETURN NEXT acc;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
According to the system, the function is created with out issue, and
there appear to not be any syntax errors being returned, however when I
execute the function in the query like this:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
The backend logger results look like:
Jan 15 13:42:56 jupiter 5439[3164]: [128-1] LOG: query: select
get_account_info('test.com');
Jan 15 13:42:56 jupiter 5439[3164]: [129-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [129-2] CONTEXT: PL/pgSQL function
"get_account_info" line 7 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [130-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [130-2] CONTEXT: PL/pgSQL function
"get_account_info" line 8 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [131-1] LOG: query: SELECT cs.id,
to_char(cs.id, 'FM0999') AS cid FROM virtual_host vh LEFT JOIN
virtual_machine vm ON (vm.id =
Jan 15 13:42:56 jupiter 5439[3164]: [131-2] vh.vm_id) LEFT JOIN
company_summary cs ON (cs.id = vm.company_id) WHERE vh.domain_name = $1
Jan 15 13:42:56 jupiter 5439[3164]: [131-3] CONTEXT: PL/pgSQL function
"get_account_info" line 10 at select into variables
Jan 15 13:42:56 jupiter 5439[3164]: [132-1] LOG: query: SELECT 'SELECT
'' || company_id.cid || '' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[132-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-3] LEFT JOIN company_' || $1 ||
'.account a ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-4] WHERE cs.id = ' || $2
Jan 15 13:42:56 jupiter 5439[3164]: [132-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [133-1] LOG: query: SELECT ' ||
company_id.cid || ' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[133-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-3] LEFT JOIN company_0011.account a
ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-4] WHERE cs.id = 11
Jan 15 13:42:56 jupiter 5439[3164]: [133-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [134-1] ERROR: set-valued function
called in context that cannot accept a set
Jan 15 13:42:56 jupiter 5439[3164]: [134-2] CONTEXT: PL/pgSQL function
"get_account_info" line 20 at return next
Can anyone see anything that I missed? Or has any suggestions?
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Problem with plpgsql function
Ack, I knew it was something small, I was under the impression that I
had been using that, it just took someone to point it out to make me
look again :>... thanks..
On Thu, 2004-01-15 at 14:47, Joe Conway wrote:
> Chris Bowlby wrote:
> > select get_account_info('test.com');
> >
> > I get this error:
> >
> > ERROR: set-valued function called in context that cannot accept a set
>
> This is the "classic" SRF error -- you need to use an SRF like a
> relation in the FROM clause, so do this instead:
>
>select * FROM get_account_info('test.com');
>
> HTH,
>
> Joe
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] comparing nulls
Hi Ken, Under 7.3.x this option was removed, you need to test via: SELECT * from table where field IS NULL; On Tue, 2004-01-20 at 09:43, Kenneth Gonsalves wrote: > in postgres7.1 i had a table where an integer field could be null. There was > no default value. a select statement like so: > 'select * from table where field = null' > would give all the rows where that field had no value. > on porting to 7.3.2, this doesnt work. How to do this? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] comparing nulls
To achieve a higher level of SQL compliancy.. On Tue, 2004-01-20 at 10:24, Kenneth Gonsalves wrote: > On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > > Hi Ken, > > > > Under 7.3.x this option was removed, you need to test via: > > > > SELECT * from table where field IS NULL; > thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around > with these thangs? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
