[SQL] Function Anomaly?
All... Given the following type and function: CREATE TYPE getnote_t AS (nid BIGINT, ownerid INTEGER, ownername VARCHAR, hostname VARCHAR, entrytime TIMESTAMP, is_active VARCHAR, is_private VARCHAR, notetext TEXT); CREATE OR REPLACE FUNCTION getnote(INTEGER, BIGINT) RETURNS getnote_t AS $getnote$ SELECT n.nid AS nid, n.ownerid AS ownerid, o.ownername AS ownername, n.hostname AS hostname, n.entrytime::TIMESTAMP(0) AS entrytime, '[' || (CASE WHEN n.is_private = TRUE THEN 'PRIVATE' ELSE 'SHARED' END) || ']' AS is_private, '[' || (CASE WHEN n.is_active = FALSE THEN 'INACTIVE' ELSE 'ACTIVE' END) || ']' AS is_active, n.notetext AS notetext FROM notes n, owners o WHERE (CASE WHEN (n.ownerid != $1 AND n.is_private IS TRUE) THEN FALSE ELSE TRUE END) AND n.ownerid = o.ownerid AND n.nid = $2 $getnote$ LANGUAGE SQL; When I call it with a row where n.is_private is TRUE and n.ownerid IS TRUE, I receive a single row of all null values: notesdb=# select * from getnote(1, 2); nid | ownerid | ownername | hostname | entrytime | is_active | is_private | notetext -+-+---+--+---+---++-- | | | | | || (1 row) When I submit the query directly (getnote.sql is simply the query with the CASE statement forced to false): notesdb=# \i getnote.sql nid | ownerid | ownername | hostname | entrytime | is_private | is_active | notetext -+-+---+--+---++---+-- (0 rows) Thanks very much in advance for any insight you can provide. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
All... In the poly_example function below, I am emulating an actual requirement by querying the same table three (3) times in order to derive a solution to a problem. Is this the best or most efficient and effective way to implement this? The table (which consists of only five (5) FLOAT8 columns) I'm querying contains less than 50 rows. Thanks in advance for any insight or criticisms you offer. CREATE OR REPLACE FUNCTION poly_example() RETURNS SETOF FLOAT8 AS $poly_example$ DECLARE term blah%ROWTYPE; sigma_l FLOAT8 := 0.0; sigma_b FLOAT8 := 0.0; sigma_r FLOAT8 := 0.0; BEGIN FOR term in SELECT * FROM blah LOOP sigma_l := sigma_l + (RANDOM() * 100) * (term.i * term.i) + RANDOM() * (term.j * term.j) + term.k; END LOOP; FOR term in SELECT * FROM blah LOOP sigma_b := sigma_b + (RANDOM() * 53) * (term.i * term.i) + (RANDOM() * 5) * (term.j * term.j) + term.k; END LOOP; FOR term in SELECT * FROM blah LOOP sigma_r := sigma_r + 96.232234 * (term.i * term.i) + 0.32322325 * (term.j * term.j) + term.k; END LOOP; RETURN NEXT sigma_l + sigma_b + sigma_r; END; $poly_example$ LANGUAGE plpgsql STRICT IMMUTABLE; -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
> But if I read the OP correctly the sigma are in fact used additively in each > row in blah. "sigma_* = sigma_* +" I apologize, but I omitted a CASE statement prior to each calculation of the values. The coefficients for each calculation change depending upon which case is valid. I could probably rewrite it using arrays and may, in fact, do so to eliminate the two added queries. Thank you for your input. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Assigning Values to Composite Types
All... Given the following [hopefully] relevant portions of a function: CREATE OR REPLACE FUNCTION solve(FLOAT8, VARCHAR, CHAR) RETURNS SETOF sometype_t AS $solve$ DECLARE data sometype_t; ... BEGIN FOR term_count IN SELECT DISTINCT coord_type AS coord_type, MAX(term) AS maxterms ... LOOP FOR i IN 0 .. term_count.maxterms LOOP SELECT SUM(a + b + c) INTO Q ... S := S + (Q * onevalue * somevalue); END LOOP; -- This is the only means of verifying/viewing the data RAISE NOTICE '% = %', term_count.coord_type, S; /* Here is where I am stuck trying to fill data I've tried: data.term_count.coord_type := S; (data.term_count).coord_type := S; data.(term_count.coord_type) := S; */ S := 0.0; END LOOP; RETURN NEXT vsop87_data; END; $solve$ LANGUAGE plpgsql STRICT IMMUTABLE; Is there a means of filling something.something.something with a value so I can get all the values into a single row? Thanks very much in advance. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 8.4.1 distinct query WITHOUT order by
All... One of the groups where I work uses a homegrown C/C++ application that was written back in 7.3 days which is now running on an 8.4.1 server (which, until very recently, was running on an 8.1 server). Unfortunately, they did not use an order by in the query and are now dealing with a return dataset that is unsorted and difficult to use. The current maintainer is unsure about being able to do the right thing and recompile the code after fixing the query. Aside from disabling enable_hashagg (which, according to the documentation, is performance-expensive), what other options do I have? What are the ramifications of renaming the table (containing 8000 rows) and creating a view of the same name? Assuming it's possible, would the efficiency of a rule to rewrite the query be an acceptable alternative? Thanks in advance for any insight and suggestions! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
> Yeah, if you're code base is that fragile, bandaging it up by jumping > through hoops in pgsql is just putting off the inevitable when it (the > code base) has to get recompiled someday anyway. I appreciate (and agree with) the concern about the fragility of the codebase. The maintainer knows that anything except adding ORDER BY is a kludge. Now, the aforementioned notwithstanding... Aside from disabling enable_hashagg (which, according to the documentation, is performance-expensive), what other options do I have? What are the ramifications of renaming the table (containing 8000 rows) and creating a view of the same name? Assuming it's possible, would the efficiency of a rule to rewrite the query be an acceptable alternative? Thanks in advance for any insight and suggestions! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Proper case function
> Has anyone got a proper case function that they are willing to share. We're > moving from SQL Anywhere to PostgreSQL and decided that much of our data is > upper case when it really should be proper cased. The columns that we will > be putting proper case data in are all defined using the contrib module > citext which makes searching very nice. Why doesn't the initcap function work as you require? -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Join Advice and Assistance
All, I've encountered a mental block due primarily to my inexperience with moderately complex joins. Given the following three tables: Table "public.users" Column | Type | Modifiers ---++--- userid| bigint | not null lname | character varying(64) | not null fname | character varying(64) | not null passwd| character varying(64) | not null is_active | boolean| not null default true Table "public.user_emailaddrs" Column | Type | Modifiers ---++--- userid| bigint | not null emailaddr | character varying(256) | not null is_active | boolean| not null default true Table "public.usermetas" Column |Type | Modifiers +-+ userid | bigint | not null startdate | timestamp without time zone | not null default now() lastlogindate | timestamp without time zone | not null default now() lastpwchange | timestamp without time zone | not null default now() logincount | integer | not null default 1 users and usermetas is a one-to-one relationship. users and user_emailaddrs is a one-to-many relationship. What is the best way to get these tables joined on userid and return all emailaddr records from user_emailaddrs (e.g. if userid has three (3) e-mail addresses in user_emailaddrs)? Is there any way to avoid returning all fields in triplicate? Please feel free to criticize where necessary. Thank you very much in advance. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join Advice and Assistance
Rob, Thanks for the reply... > If you want records for user without email addresses you will need an outer > join on user_emailaddrs > > /* untested */ > select u.userId, u.lname, u.lastname ,m.startdate, a.emailaddr > from users u > join usermetas m on u.userid = m.userid > left join user_emailaddrs a on m.userid = a.userid My question was related more toward eliminating the query returning a record for each record in the one-to-many table. I see now that I'm going to have to aggregate the e-mail addresses in order to return a single row. Thanks again. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join Advice and Assistance
Stephen,
> If you want to return a single row for each user, regardless of the number
> of email addresses, you might use ARRAY() with a subquery, eg (haven't
> tested this to make sure it completely works):
Your query worked perfectly!
> Of course, this will return the addresses as a character varying[], with
> output like {u...@domain.tld,u...@domain.tld}, and would require some minor
> contortions to present it to users cleanly. The array_to_string function may
> help you make it easier to display the results.
Absolutely -- and thank you for the suggestion. I'll be retrieving
the results of the query through PHP, so cleanup in the query may even
be a performance degradation.
> Hope this helps,
You and Rob Sargent have helped a great deal. Thanks to both of you.
-- Gary Chambers
/* Nothing fancy and nothing Microsoft! */
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Inserting Multiple Random Rows
All, I have a table of user addresses that I'm trying to randomly populate with data from a cities table. Using the following query: INSERT INTO useraddrs(userid, addrdesc, city, stprov) SELECT u.userid, 'Home', c.cityname, c.stateabbr FROM users u, cities c WHERE u.userid NOT IN (SELECT userid FROM useraddrs) AND cid=(SELECT cid FROM cities ORDER BY RANDOM() LIMIT 1); I am able to achieve most of what I am trying to accomplish, but once the random number is selected, it doesn't change. What am I missing? Thank you in advance. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Tom, > I believe the sub-SELECT will only get executed once, since it has no > dependency on the outer query. That seems to be the behavior its exhibiting. > What were you expecting to happen? The clouds parting, choirs of angels singing, and fireworks celebrating the veil of my obtuseness being lifted, and my grasp and command of SQL to be complete and infinite. None of which appears will ever happen... -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Justin, Thanks for the reply! > you need to generate a series of random numbers then select each record > one at a time out of cities table . You will have to write a plsql > function to do this As any join will cause the result to be ordered. After modifying my search terms at Google, I've discovered that I'm not alone in this endeavor. There are a couple of informative articles, the most beneficial of which is written by depesz at http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ and the one that referred me to it at http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/ > Here is example of pl-sql procedure that inserts records randomly into a > table from a another table. I could have been done with this in pretty short order if I had just continued with writing a quick PL/pgSQL function to do it. It's all Quassnoi's fault that I abandoned my original plan and tried to solve the problem with a query. After all, I need to learn to think in sets and get out of the procedural mindset, but I still haven't heard the click! :) Thanks for the suggestion in your follow-up e-mail. I'll post the solution upon discovering it. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Justin (et al), > You now what might work In the interest of efficiency, I abandoned the quest for the perfect query and wrote my own function and used a modified version of depesz's get_random_id() function: CREATE OR REPLACE FUNCTION gen_fake_addresses() RETURNS INTEGER AS $gen_fake_addresses$ DECLARE v_uid BIGINT; v_cid INTEGER; v_cst RECORD; v_count BIGINT := 0; BEGIN FOR v_uid IN SELECT userid FROM users WHERE userid NOT IN (SELECT userid FROM useraddrs) LOOP SELECT INTO v_cid get_random_city(); SELECT INTO v_cst cityname, stateabbr FROM cities WHERE cid = v_cid; INSERT INTO useraddrs(userid, addrdesc, city, stprov) VALUES (v_uid, 'Home', v_cst.cityname, v_cst.stateabbr); v_count := v_count + 1; END LOOP; RETURN v_count; END; $gen_fake_addresses$ LANGUAGE plpgsql VOLATILE; /* This is depesz's */ CREATE OR REPLACE FUNCTION get_random_city() RETURNS INT4 AS $get_random_city$ DECLARE id_range RECORD; reply INT4; try INT4 := 0; BEGIN SELECT min(cid), max(cid) - min(cid) + 1 AS range INTO id_range FROM cities; WHILE (try < 10) LOOP try := try + 1; reply := FLOOR(RANDOM() * id_range.range) + id_range.min; PERFORM cid FROM cities WHERE cid = reply; IF FOUND THEN RETURN reply; END IF; END LOOP; RAISE EXCEPTION 'No record found in % tries', try; END; $get_random_city$ LANGUAGE plpgsql STABLE; I'd like to thank Justin Graf for his time, effort, and assistance with this problem. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] user function and bind
David,
> I need an example how to write user function with columns binding and how to
> use it on PHP
If I'm understanding your request correctly:
$dbh = pgpconnect("dbname=db host=dbhost user=dbuser password=pw");
$query = 'SELECT * FROM table WHERE x=$1 AND y=$2 AND z=$3';
$stmt = pg_query_params($dbh, $query, array($x, $y, $z));
-- Gary Chambers
/* Nothing fancy and nothing Microsoft! */
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Duplicates Processing
All, I've been provided a CSV file of parts that contains duplicates of properties (e.g. resistors have a wattage, tolerance, and temperature coefficient property) of those parts that differ by a manufacturer part number. What I'd like to do is to process this file and, upon encountering one of the duplicates, take that part with its new part number and move it to a part substitutes table. It seems like it should be pretty simple, but I can't seem to generate a query or a function to accomplish it. I'd greatly appreciate any insight or assistance with solving this problem. Thank you very much in advance. -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Duplicates Processing
Tim, Thanks for taking the time to reply! > | INSERT INTO substitutes ([...]) > | SELECT [...] FROM > | (SELECT *, > | ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature > | ORDER BY part_number) AS RN > | FROM parts) AS SubQuery > | WHERE RN > 1; > | DELETE FROM parts > | WHERE primary_key IN > | (SELECT primary_key FROM > | (SELECT *, > | ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature > | ORDER BY part_number) AS RN > | FROM parts) AS SubQuery > | WHERE RN > 1); You have solved the problem precisely as I described it. In my haste to make the request for assistance, I omitted one critical piece of information that may call into question my data model. In its current state, my substitute parts table contains only the part number (the "new" one, so-to-speak), a foreign key reference to the original parts table, and some location data (which is also in the original parts table). Is there any advice you can offer in light of what I have just described? I apologize for the oversight. -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Duplicates Processing
Rob, > Perhaps a trade off between nullable fields and redundant types. If > your original table simply had a nullable column called > isReplacementFor, into which you place in the subsequent rows the id of > the first instance found. Am I misunderstanding you when you're suggesting a table like: part_number INTEGER is_replacement_for INTEGER references part_number value INTEGER wattage FLOAT8 ... -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Duplicates Processing
Rob, > Yes. With this you can find all part numbers/supplies which match your > value, wattage criteria in one table. Or exclude any which have a > non-null is_replacement_for value. I understand -- thanks. I have received contradictory advice in a purely data modeling context. What about the null values that will be in the properties columns of the part? It would appear to be more applicable to an employee database where the columns are populated regardless and the "replacement_for" in the context of our discussion would be a self-reference to the employee's manager. No? Thanks again for your help. -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Duplicates Processing
Rob, Thanks for your reply! > And to your point of self-reference, it would be to a co-worker more > than a manager. Managers are often not good replacements for workers. :) :) Absolutely! I was having a conversation over on #postgresql yesterday about this and, due to my inexperience with managing electronic components (and some of the database issues involved), I still have a few unanswered questions. I would like to create a master table of parts derived from the individual tables (e.g. resistors, capacitors, diodes, etc.). I have the rare opportunity to build this from the ground, up and would like to ensure that I get it right. Thanks for any advice that you (or anyone) can offer. -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sqlplus reporting equivalent in postgres?
psql - not as advanced, doesn't have all the features SQL*Plus has. On the other hand, it is at least capable of command history and readline support. Yes, but rlwrap will eliminate that limitation. -- Gary Chambers -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] system tables inquiry & db Link inquir
I want to find all the sequences. What is the system tables that have the information about all the sequences? psql -E -U \ds Capture the query that psql sends to the server. Can't help with the dblink -- sorry. -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 1: 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] Encrypted column
I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a Take a look at the pgcrypto user-contributed module. -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Query Assistance
All... I have a simple table in PostgreSQL 8.2.5: CREATE TABLE power_transitions ( -- Transition ID (PK) tid integer NOT NULL, -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery) sid smallint NOT NULL, -- Timestamp of transition statetime timestamp without time zone DEFAULT now() NOT NULL, -- Is this a real outage? is_outage boolean DEFAULT true NOT NULL ); It contains a log of power outages (transitions). I'd like to create query that returns a transition offline time and associated return to online time. Is there a better way of handling this? I am open to schema change suggestions. Thanks very much! -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query Assistance
D'Arcy... > Have you considered this? I considered integrating the begin and end times into the table. I'm capturing the data via ACPI events, so it's "transactional" by nature. I want to be able to keep track of false transitions (hence the is_outage field). I'm looking for a way to simplify the transitions output on my web browser, and I want to combine an offline and a corresponding online time into a single line. > I am also thinking of a scheme that uses two tables but I don't really > know your environment or requirements. I am assuming that you spend > more time querying the table than updating it. If not your problem > isn't your database, it's your power plant. :-) It's nothing complex at all. The power in my neighborhood is infamously unstable, and I'm merely keep records to complain as much as I can to the power company. Thanks for taking the time to reply! -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
>> I have one problem with the user table. I want to hide the password for the
>> users.
Here's what I did, which requires using the contrib/pgcrypto extension:
CREATE FUNCTION encryptpw() RETURNS TRIGGER AS
$encryptpw$
BEGIN
NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));
RETURN NEW;
END;
$encryptpw$
CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON assignees
FOR EACH ROW EXECUTE PROCEDURE encryptpw();
Comments, suggestions, criticisms?
-- Gary Chambers
/* Nothing fancy and nothing Microsoft! */
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
> The weakness of this solution is that your password might be send in the > clear through the network as the encription ocurrs in the database. I > suggest the encryption be enforced at the application or secure the > connection with ssl. Absolutely -- and understood. I should have been more specific in my request for comments since I was only thinking about PostgreSQL-specific issues related to what I presented to the initial requestor. Thanks for your reply! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
