Re: [SQL] pivot-like transformation
Joe Conway schrieb: Torsten Lange wrote: Hello, I have a table with measurement values and columns like this: analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error) With PL/PgSQL at the end I want try to perform a pivot-like arrangement of these data: sample_id|Temperature [?C]|parameter2 [mg/L]|...|parameterN [L/year] -++-+---+--- 5| 23.00|0.036|...| 35.1 Not a direct answer with respect to plpgsql, but for pivot table functionality see the contrib/tablefunc function crosstab(). I don't like the idea to have a table in another schema in order to create a pivot-like arrangement. Anyway, this wasn't really my need. I got stuck at the very basics (sorry for that) - reading data from a table, doing something with them, like printing to the screen. I tried the example at http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES and got the error message something like "table_name returns more then one row...". I cannot recall it accurately since I did it at home and now I'm at work. I also found very few sources about handling of arrays and how to fill them up with query results... things like this. Does anyone know a more comprehensive source? Have you looked at the online docs? http://www.postgresql.org/docs/7.4/static/arrays.html http://www.postgresql.org/docs/7.4/static/functions-array.html http://www.postgresql.org/docs/7.4/static/functions-comparisons.html http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS ...at first Best regards, Torsten ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] feature request
hello it might be stupid... sometimes i am starving UPDATE OR INSERT command thnx ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Function Parameters - need help !!!
Dear Pradeep , CREATE PROC PP_ReadPointByValue @SessionID int = NULL, --these r default parameters SessionID INT := NULL; @SPSID int = 1,--default value SPSID int := 1 ; @ParameterName nvarchar (50) = NULL, -- if NULL read all parameters ParameterName varchar(50) := NULL ; @NumValue real = NULL, @StrValue nvarchar (255) = NULL, same as ParameterName varchar(50) := NULL ; @ParameterID int = NULL ParameterID int := NULL; Kindly note : 1. This all is for plpgsql procedure 2. All the variable in upcase would be used as lower case i.e SessionID would be sessionid untll it is not as "SessionID" 3. If a value is not intiliazed it is defaulted to NULL. -- Best Regards, Vishal Kashyap Director / Lead Software Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com [Comming Soon http://www.saihertz.com] Yahoo IM: coeb_college[ a t ]yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Strange behaviour updating primary key column.
Luis Neves wrote: In the above table why does the query: UPDATE "story" SET id = (id + 1500); fails with: "ERROR: duplicate key violates unique constraint 'story_pkey'" (I have 6000 records in the table with "id" spanning from 1 to 6000) I find this behaviour strange, SQL is a set based language, but PostgreSQL is acting like is changing the values in sequence. Quite right - it shouldn't happen. The issue is that the unique check isn't deferred, but is made whenever a value changes. So 1=>1501 of course gives the error. The work-around usually given is to do two updates: UPDATE story SET id = -id; UPDATE story SET id = -id + 1500; The real solution would be to check unique constraints at the end of statement, but I assume this is a tricky change or it would have been done by now. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] subselect prob in view
Hi folks, I've got the view: create view nrequest_details as select r.r_id, r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,r.r_pack_mats, r.r_delivery, r_delivery::date-now()::date as r_remaining, r.r_created, r.r_completed, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des from requests r, users u, request_types t, request_states s, dealerships d, departments de, customers c where r_d_id = d.d_id and r_s_id = s.s_id and r_c_id = c.c_id and r_t_id = t.t_id and r_d_id = d.d_id and r_de_id = de.de_id and r_u_id = u.u_id; to which I want to add a count (2 eventually), so that it becomes: create view nrequest_details as select r.r_id, r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,r.r_pack_mats, r.r_delivery, r_delivery::date-now()::date as r_remaining, r.r_created, r.r_completed, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.count as comments -- cor.count as comments_unseen from requests r, users u, request_types t, request_states s, dealerships d, departments de, customers c left outer join (select co_r_id, count(co_r_id) from comments group by co_r_id) co on co.co_r_id = r.r_id --left outer join (select co_r_id, count(co_r_id) from comments where cor_viewed is null -- group by co_r_id) co on -- co.co_r_id = r.r_id where r_d_id = d.d_id and r_s_id = s.s_id and r_c_id = c.c_id and r_t_id = t.t_id and r_d_id = d.d_id and r_de_id = de.de_id and r_u_id = u.u_id; but I get the error: [EMAIL PROTECTED] gary]$ psql -f goole1.sql DROP psql:goole1.sql:45: ERROR: Relation "r" does not exist [EMAIL PROTECTED] gary]$ I tried using the table name instead of the alias but instead got the error: [EMAIL PROTECTED] gary]$ psql -f goole1.sql psql:goole1.sql:1: ERROR: view "nrequest_details" does not exist psql:goole1.sql:45: NOTICE: Adding missing FROM-clause entry for table "requests" psql:goole1.sql:45: ERROR: JOIN/ON clause refers to "requests", which is not part of JOIN [EMAIL PROTECTED] gary]$ which at least makes sense. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Function Parameters - need help !!!
Well this is right for the local variablesbut what about the function parameters. Okay, I will make it more simplesay I want to write a function like this CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS ' DECLARE param1 ALIAS FOR $1; param2 ALIAS FOR $2; BEGIN --- -- --- END; ' LANGUAGE 'plpgsql'; Now i want to define default values to param1 and param2if i dont pass a valuethe function should take default values for those arguments.that is what i was trying to implementneed help on this -Original Message- From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] [mailto:[EMAIL PROTECTED] Sent: Monday, June 21, 2004 3:12 PM To: Pradeepkumar, Pyatalo (IE10) Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Function Parameters - need help !!! Dear Pradeep , >CREATE PROC PP_ReadPointByValue >@SessionID int = NULL, --these r default parameters > > SessionID INT := NULL; >@SPSID int = 1,--default value > > SPSID int := 1 ; >@ParameterName nvarchar (50) = NULL, -- if NULL read all parameters > > ParameterName varchar(50) := NULL ; >@NumValue real = NULL, >@StrValue nvarchar (255) = NULL, > > same as ParameterName varchar(50) := NULL ; >@ParameterID int = NULL > > ParameterID int := NULL; Kindly note : 1. This all is for plpgsql procedure 2. All the variable in upcase would be used as lower case i.e SessionID would be sessionid untll it is not as "SessionID" 3. If a value is not intiliazed it is defaulted to NULL. -- Best Regards, Vishal Kashyap Director / Lead Software Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com [Comming Soon http://www.saihertz.com] Yahoo IM: coeb_college[ a t ]yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Function Parameters - need help !!!
Hi, Quote from Section 37.11 of the manual: # There are no default values for parameters in PostgreSQL. # You can overload function names in PostgreSQL. This is often used to work around the lack of default parameters. So for your example: > CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS ' > ... you should be able to write: CREATE FUNCTION test(integer) RETURNS INTEGER AS ' BEGIN test($1, default_value_for_param2); END; ' LANGUAGE 'plpgsql'; and also: CREATE FUNCTION test() RETURNS INTEGER AS ' BEGIN test(default_value_for_param1); END; ' LANGUAGE 'plpgsql'; Hope this is what you were looking for. --Phil. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function Parameters - need help !!!
Pradeepkumar, Pyatalo (IE10) wrote: Well this is right for the local variablesbut what about the function parameters. Okay, I will make it more simplesay I want to write a function like this CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS ' DECLARE param1 ALIAS FOR $1; param2 ALIAS FOR $2; BEGIN --- IF param1 IS NULL THEN param1 := 1234; END IF; Now i want to define default values to param1 and param2if i dont pass a valuethe function should take default values for those arguments.that is what i was trying to implementneed help on this Now if you want to be able to call function test like so: SELECT test(1) Then you can't. The workaround is to define two functions: CREATE FUNCTION test(integer,integer) CREATE FUNCTION test(integer) Function #2 calls function #1 with the 2nd paramter pre-defined. PG is quite strict about it's type-matching, which is why you need to have two entries for the function. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] subselect prob in view
Gary Stainburn <[EMAIL PROTECTED]> writes: > from requests r, users u, request_types t, > request_states s, dealerships d, departments de, customers c > left outer join (select co_r_id, count(co_r_id) from comments group > by co_r_id) co on > co.co_r_id = r.r_id > psql:goole1.sql:45: ERROR: Relation "r" does not exist I think you have learned some bad habits from MySQL :-( PostgreSQL follows the SQL spec and makes JOIN bind more tightly than comma. Therefore, in the above the LEFT JOIN is only joining "c" to "co" and its JOIN ON clause can only reference those two relations. You could get the behavior you seem to expect by changing each comma in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right and so "r" will be part of the left argument of the LEFT JOIN. Note that if you are using a pre-7.4 release this could have negative effects on performance --- see the user's guide concerning how explicit JOIN syntax constrains the planner. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] subselect prob in view
On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > from requests r, users u, request_types t, > > request_states s, dealerships d, departments de, customers c > > left outer join (select co_r_id, count(co_r_id) from comments > > group by co_r_id) co on > > co.co_r_id = r.r_id > > psql:goole1.sql:45: ERROR: Relation "r" does not exist > > I think you have learned some bad habits from MySQL :-( > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than > comma. Therefore, in the above the LEFT JOIN is only joining "c" to > "co" and its JOIN ON clause can only reference those two relations. > > You could get the behavior you seem to expect by changing each comma > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right > and so "r" will be part of the left argument of the LEFT JOIN. > > Note that if you are using a pre-7.4 release this could have negative > effects on performance --- see the user's guide concerning how explicit > JOIN syntax constrains the planner. > > regards, tom lane Thanks for this Tom, but I've never used MySQL. I'll look at the docs and have another go. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [JDBC] Prepare Statement
Does plperl catch the plan also? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 2:47 PM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Fri, 18 Jun 2004, Jie Liang wrote: > However, I am still thinking if I call one SELECT and one DELECT and > one UPDATE and one INSERT a thousand times against same table with > different arguments, should I consider performance iusse? Right, this is a case where some benefits can be found, but remember the premature optimization adage. > > Secondly, I assume the function should be a pre-compiled object stored > on server side, doesn't it. > I depends on the language the function is written. plpgsql caches plans, but not all procedural languages do. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] plpgsql - Insert from a record variable?
Phil> Insert from a record variable? Riccardo> Try insert into table select r.*; Tom> in 7.5 Tom> insert into table select r.*; Tom> insert into table values(r.*); Tom> so long as r is declared to be of a named rowtype (not just Tom> RECORD) Thanks! Unfortunately I need record, rather than %rowtype. See my later email where I describe how I am trying to use this with inheritance; the function looks up a row in a base table, finds the derived table in which it really exists using pg_class.relname, and then inserts a modified copy of the row in the derived table. I'm not concerned about the performance issues to do with pre-planning the queries. I think what I really need is an introspection mechanism so that I can loop over each element of the record and construct the insert as a string. Maybe this is possible using a different server-side language? I've just had an idea: perhaps rather than inspecting the record variable to see what fields it contains, I can look at the table to see what columns it contains (since this amounts to the same thing). Presumably I can do this using information_schema.columns. I'll have a go. --Phil. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql - Insert from a record variable?
I wrote:
> perhaps rather than inspecting the record variable to see what fields
> it contains, I can look at the table to see what columns it contains
This is my effort. It doesn't work, because I can only EXECUTE
SQL statements, not PLPGSQL statements. I need to EXECUTE an
assignment statement to accumulate the string of column values.
I have a feeling that I can EXECUTE a CREATE FUNCTION statement,
and then call the function, but this seems over-the-top. I just
want to insert a record into a table! Any better ideas?
-- Simply insert record r into table t.
-- Doesn't work, because EXECUTE takes an SQL command, not
-- a plpgsql statement.
create function insert_record ( record, text ) as '
-- probably ought to pass schema as well as table name, since
-- information_schema.columns query doesn't use search_path.
declare
r as alias for $1;
t as alias for $2;
cr information_schema.columns%rowtype;
first boolean;
column_names text;
column_values text;
begin
first := true;
for cr in select * from information_schema.columns
where table_name=t loop
if not first then
column_names := column_names || '', '';
column_values := column_values || '', '';
first := false;
end if;
column_names := column_names || quote_ident(cr.column_name);
!! execute ''column_values :=
!! column_values || quote_literal(r.'' || cr.column_name || '')'';
end loop;
execute ''insert into '' || t || ''('' || column_names ||
'') values ('' || column_values || '')'';
end;
' language plpgsql;
--Phil.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] plpgsql - Insert from a record variable?
Phil Endecott wrote: > !! execute ''column_values := > !! column_values || quote_literal(r.'' || cr.column_name || '')''; I'm guessing you want something like FOR rec IN EXECUTE ''select column_values || quote_literal(r.'' || cr.column_name || '') alias column_values''; LOOP column_values := rec.column_values; END LOOP; another suggestion, rather than do >if not first then > column_names := column_names || '', ''; > column_values := column_values || '', ''; > first := false; >end if; why not use two arrays and append the name/values using array_append() and use array_to_string() to join them after that? -- basic ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql - Insert from a record variable?
"Phil Endecott" <[EMAIL PROTECTED]> writes: > I think what I really need is an introspection mechanism > so that I can loop over each element of the record and construct the > insert as a string. Maybe this is possible using a different > server-side language? pltcl can probably handle this; I'm less sure about plperl or plpython. (No reflection on the languages, but pltcl has the most complete Postgres interface.) 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
