Re: [SQL] Question about functions
Mike, > I am trying to run this function but the return is not > correct. If I run the select statement from the psql command > line it works. My guess is that the WHERE clause could be > causing the problem. Then again, it may be how I am using > the FOR loop. The ides column is of type TEXT. > > CREATE OR REPLACE FUNCTION sp_description_search(varchar) > RETURNS varchar AS $$ -- <== Problem is here > DECLARE > myrec record; > BEGIN > FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP > RETURN NEXT myrec;-- <== Problem is here > END LOOP; > RETURN; > END; > $$ LANGUAGE 'plpgsql'; You need to declare this as a set-returning function if you're using RETURN NEXT. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question about functions
Mike Plemmons wrote: I am trying to run this function but the return is not correct. PS - next time, details of what it *did* return and why that wasn't correct would be useful. Not needed in this case, but good practice anyway. PPS - the subject line was pretty good, but better might have been "Problem with LIKE and variables in a plpgsql function" PPPS - This is a fairly inefficient way of running simple queries. S - Thanks for the question! -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Question about functions
Mike Plemmons wrote: I am trying to run this function but the return is not correct. If I run the select statement from the psql command line it works. My guess is that the WHERE clause could be causing the problem. Then again, it may be how I am using the FOR loop. The ides column is of type TEXT. FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP You're assuming plpgsql does variable interpolation in a similar way to Perl/shell etc. It doesn't. You'll want something like: ...LIKE '%' || $1 || '%' -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question about functions
Mike Plemmons wrote: These are great tips! I will be more specific the next time I post. Regarding the ineffiencient query. What do you suggest as a better way? I would rather learn proper methods than continue to use improper ones. Just send the query directly from the application - the way you're doing it, the function will have to assemble the query, execute it, fetch all the results and then return them to your application. Of course, if your query altered each row as it fetched them (e.g. to calculate a running total) then this would be a sensible approach. PS - don't forget to CC: the list when replying - that's the convention around here. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Design problem : using the same primary keys for inherited objects.
> >I give a clearer example : > >CREATE TABLE actor ( > >id_actor serial PRIMARY KEY, > >arg1 type1, > >arg2 type2 > >) > >CREATE TABLE person ( > >id_person INTEGER PRIMARY KEY REFERENCES actor, > >arg3 type3, > >arg4 type4 > >) > >Don't you think it is a BAD design ? > >If it isn't, well, it will expand my database practices. > > It *is* a bad design. You should not do this. After all, how is that > any different than this? > CREATE TABLE actor_person ( > id_actor serial PRIMARY KEY, > arg1 type1, > arg2 type2 > arg3 type3, > arg4 type4 > ) > Furthermore, inheritance is almost certainly the wrong relationship type > here. Normally, Actor would be a Role that a Person would be playing: Oups, I've made a vocabulary mistake. By 'actor', I meant "somebody who does something". Lots of tables inherits from 'actor' in our current design, each of these being a "job" : laboratory, delivering company, etc... Furthermore, 'person' inherits from 'actor', and some other tables inherits from 'person' : user, physician, customer, etc... Do you continue to think that inheritance is the wrong relationship type here ? > It *is* a bad design. You should not do this. After all, how is that > any different than this? Well, not every line of actor are in laboratory, as some are in person, and in some moreother tables. So, it is different, isn't it ? -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Design problem : using the same primary keys for inherited objects.
> That is perfectly valid. Only, I would argue that an actor is a person. Oups, i really made a vocabulary mistake here. Let me paste what i wrote some minutes earlier to Daryl : By 'actor', I meant "somebody who does something". Lots of tables inherits from 'actor' in our current design, each of these being a "job" : laboratory, delivering company, person, etc... Furthermore, 'person' inherits from 'actor' and some other tables inherits from 'person' : user, physician, customer, etc... > What I was offering was dealing with issues where more then one actor could > be the same person. Given your design, a person could only be one actor. If > that is true, no more discussion is needed. If that is not true, then one > way to deal with that is to make compound primary keys in your actor table. > table person ( > person_id serial primary key, > name varchar(20)); > table actor( > person_id foreign key references person, > role varchar(20), > primary key ( person_id, role ) > ); > would then allow a person to be more then on actor based on role. It is specified that not two laboratories could be the same actor, for example. That's why i think having a primary key referencing another primary key could technically work, but well, it seems to me BAD to mix two primary keys together. I'm still wondering... -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Design problemi : using the same primary keys for inherited objects.
Well yes, i find your system very interesting and will maybe use it as
solution for another problem we have ( :-) ), but i fail to see where
it makes use of a primary key referencing another primary key ?
On Fri, Oct 14, 2005 at 08:29:40AM -0700, codeWarrior wrote:
> I dont consider this to be a design problem... In fact... I do the inherited
> table thing pretty routinely... It (table inheritance) works very well if
> you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
> system without the overhead of managing transactions.
>
> Consider the relationship between a company (sys_client) and the company's
> employees (sys_user). An inheritance model in postgreSQL is a very efficient
> mechism to ensire that user entries "roll-up-to" or are "owned" by a client
> entry in the parent table. Here's a short example:
>
> If you wish -- you can execute the following DDL.
>
> CREATE TABLE sys_client (
>
> id SERIAL NOT NULL PRIMARY KEY,
>
> c_name VARCHAR(72),
> lu_client_type INTEGER NOT NULL DEFAULT 4 REFERENCES lu_client_type,
> lu_support_program INTEGER NOT NULL REFERENCES lu_support_program(id),
>
> create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
> change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
> change_id INTEGER DEFAULT 0,
> active_flag BOOLEAN NOT NULL DEFAULT TRUE
>
> ) WITH OIDS;
>
> CREATE TABLE lu_user_type (
>
> id serial NOT NULL PRIMARY KEY,
> type_desc varchar(72) NOT NULL,
> can_delete bool DEFAULT false,
>
> create_dt timestamp NOT NULL DEFAULT now(),
> change_dt timestamp NOT NULL DEFAULT now(),
> change_id INTEGER NOT NULL DEFAULT 0,
> active_flag bool NOT NULL DEFAULT true
>
> ) WITH OIDS;
>
> INSERT INTO lu_user_type(type_desc) VALUES ('Administrator');
> INSERT INTO lu_user_type(type_desc) VALUES ('User');
> INSERT INTO lu_user_type(type_desc) VALUES ('Restricted user');
> INSERT INTO lu_user_type(type_desc) VALUES ('Demo');
> INSERT INTO lu_user_type(type_desc) VALUES ('Demo - admin');
>
> CREATE TABLE sys_user (
>
> sys_client_id INTEGER NOT NULL REFERENCES sys_client(id),
> lu_client_group INTEGER references sys_client_group(id),
>
> lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
> f_name VARCHAR(50) NOT NULL,
> m_name VARCHAR(50),
> l_name VARCHAR(50) NOT NULL,
> email_addr VARCHAR(120) NOT NULL,
> uname VARCHAR(20) NOT NULL,
> upwd VARCHAR(20) NOT NULL,
>
> login_allowed BOOLEAN DEFAULT true,
> reset_pwd BOOLEAN DEFAULT false,
> pwd_change_reqd bool DEFAULT false,
>lost_passwd bool DEFAULT false
>
> ) INHERITS (sys_client);
>
>
> CREATE TABLE sys_user_history (
>
>hist_id SERIAL NOT NULL PRIMARY KEY,
>hist_dt TIMESTAMP NOT NULL DEFAULT NOW()
>
> ) INHERITS (sys_user);
>
> CREATE OR REPLACE RULE sys_user_history AS ON UPDATE TO sys_user DO INSERT
> INTO sys_user_history (SELECT * FROM ONLY sys_user WHERE id = OLD.id);
>
> CREATE TABLE sys_user_login (
>
> id serial NOT NULL PRIMARY KEY,
>
> sys_client INTEGER NOT NULL REFERENCES sys_client(id),
> login_date timestamp NOT NULL DEFAULT now(),
> valid_until timestamp NOT NULL DEFAULT (now() + '00:20:00'::interval),
> session_id varchar(32) NOT NULL UNIQUE,
>
> create_dt timestamp NOT NULL DEFAULT now(),
> change_dt timestamp NOT NULL DEFAULT now(),
> change_id int4 NOT NULL DEFAULT 0,
> active_flag bool NOT NULL DEFAULT true
>
> ) WITH OIDS;
>
>
> insert into sys_client(c_name) VALUES ('Mattel');
> insert into sys_client(c_name) VALUES ('Hasbro');
> insert into sys_client(c_name) VALUES ('Lego');
>
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (1, 1, 'Arnold', 'Antione', 'Aardvaark',
> '[EMAIL PROTECTED]', 'arnie', 'arnie');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (1, 2, 'Roberto', 'Guiterrez', 'Amendola',
> '[EMAIL PROTECTED]', 'arnie', 'arnie');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (2, 1, 'Albert', '', 'Einstien',
> '[EMAIL PROTECTED]', 'albert', 'albert');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (2, 2, 'David', 'C', 'Davidson',
> '[EMAIL PROTECTED]', 'david', 'david');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (3, 1, 'Marilyn', '', 'Monroe',
> '[EMAIL PROTECTED]', 'mmonroe', 'mmonroe');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (3, 2, 'Fred', 'E', 'Flintstone',
> '[EMAIL PROTECTED]', 'fred', 'fred');
>
>
>
> SET SQL_INHERITANCE = ON;
>
> -- RUN EACH OF THESE QUERIES IN SUCESSION TO SEE HOW IT REALLY WORKS...
> SELECT * FROM sys_client;
> SELECT * FROM ONLY sys_client;
> SELECT * FROM sys_user;
> SELECT * FROM ONLY sys_user;
>
> SELECT SC.id AS client_id, SC.c_name, SU.id AS employee_id, S
Re: [SQL] Design problemi : using the same primary keys for inherited
David Pradier wrote: Well yes, i find your system very interesting and will maybe use it as solution for another problem we have ( :-) ), but i fail to see where it makes use of a primary key referencing another primary key ? As regards the issue of one primary-key referencing another, I can't see any problem. You want 0 or 1 references from table B to any row in table A - this does that without any fuss or complication. A primary key is a value (or set of values) like any other. It is unique over any one table, but nothing says it can't be the same as a value in some other table. The other way would be something like: CREATE TABLE A ( id serial not null unique, aval text, primary key (id) ); CREATE TABLE B ( id serial not null unique, aref int4 not null unique references A, bval text, primary key (id) ); So - in table B we now have two candidate keys (id, aref) and above I've chosen "id" as the primary-key. But I can eliminate "id" completely and not lose any of the meaning of table "B" - which tells me that I was wrong to think the rest of the table was dependent on "id". So, I must have been wrong in making "id" a primary-key and since it has no meaning of its own, I can eliminate it. CREATE TABLE B ( aref int4 not null unique references A, bval text, primary key (aref) ); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
