Re: [SQL] weird structure
Does it have to be in one row? Otherwise, assuming that person_data.d_person_id references person.a_id and person_data.d_attribute_id references person_attribute.a_id: select a.a_name from person p, person_data d, person_attribute a where p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND d.d_attribute_id = a.a_id Would return a list of attributes the person has, one per row. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 24, 2000 11:24 AM Subject: [SQL] weird structure > Hi, > > Consider the following tables/fields: > table "person": fields "p_id", "p_name". > table "person_attribute": fields "a_id", "a_name". > table "person_data": fields "d_person_id", "d_attribute_id", > "d_value". > > Also consider that a person may not have data related to all possible > attributes. > > Using this structure, how could I retrieve in one row with a single > select statement all attributes from a person (showing null to > attributes that were not registered to him)? > > Thanks for any suggestion, > -- > Renato > Sao Paulo - SP - Brasil > [EMAIL PROTECTED] >
Re: [SQL] Viewing a function
Try "SELECT prosrc FROM pg_proc WHERE proname = 'funcname'", where funcname is the name of the function you want to see. - Original Message - From: "stuart" <[EMAIL PROTECTED]> To: "PG-SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 29, 2000 3:20 AM Subject: Fw: [SQL] Viewing a function > > -Original Message- > From: Stuart Foster <[EMAIL PROTECTED]> > To: PG-SQL <[EMAIL PROTECTED]> > Date: Wednesday, 30 August 2000 2:25 > Subject: [SQL] Viewing a function > > Helllo Stuart, > > Good question. > I have been fiddly with a function editor using zeos controls and I have > lots of little problems, so that I have given up for a while. > > I successfully parsed function information. (The following is from memory > and I dont have postgres with me right now). > The body of the function is in pg_lang.prosrc (I think) or something like > that > (I found out most stuff by looking in the c code for psql. I work in > delphi) > You can work out param numbers from another field and work out the field > types > (I confess Icheated here. You can get field type out of system tables but I > never actually worked out how). > > I could reconstruct a nice looking create function string using data in the > data base. > When I tried it execute things from windows things went wrong but it work in > psql. > I ended up just using it as a utility to create separated create function > files wich I then loaded server side. > > Anyway... then problems. > I dont think I ever had a successful update from client side. > Once I committed a change I often could not successfully drop and create a > function and vacuum would fail. > To recover I had to drop all functions and re-create them. > Using PERFORM seemed to cause particular problems. (Think that anything > that is called by perform must be compiled in before anything that calls it. > Order of creation seems to important. A full dump and create would fix > these sort of problems) > The cr/lf thing also gave problems although I wrote what I thought was a > client side fix for this. > > You can have my source in Delphi (such as it is) if you are interested. > > I think there must be flags or dependencies with function managemnt which I > dont understand. > > PS I have not had success editing function in pgaccess either (similar > problems) > > A simple way to view functions is pg_dump -c -s database to just dump schema > and drop statements. Cut and paste function definitions. > > The difficulty of function editing has retarded my putting tasks server-side > which belong there. > > >How can a view a function after it's created ? > >I've created a SQL function that I need to review and possibly change. What > >is the best way to go about this. > > > > > >TIA > > > > Another stuart >
