Re: [SQL] alter sequence + subqueries
On Fri, Sep 16, 2005 at 06:36:48PM -0400, jeff sacksteder wrote: > ALTER SEQUENCE foo_id_seq > RESTART WITH (SELECT max(id) FROM foo); > > Is this syntax correct and supported? Not according to the documentation; for an alternative see the example under "User Comments": http://www.postgresql.org/docs/8.0/interactive/sql-altersequence.html -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] plpgsql function not accepting NULL value
On 9/16/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote: > > create or replace function update_rate (integer, integer, integer, > > integer, numeric, integer) > > You've created a function with six arguments. > > > invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216); > > ERROR: function update_rate(integer, integer, integer, "unknown", > > numeric, integer, integer) does not exist > > You've called a function with seven arguments. Which is it supposed > to be? > Ahh... thanks to both of you. My eyes are tired. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql function not accepting NULL value
On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote: > create or replace function update_rate (integer, integer, integer, > integer, numeric, integer) You've created a function with six arguments. > invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216); > ERROR: function update_rate(integer, integer, integer, "unknown", > numeric, integer, integer) does not exist You've called a function with seven arguments. Which is it supposed to be? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] plpgsql function not accepting NULL value
On Fri, 16 Sep 2005, Kenneth Dombrowski wrote: > I can't get this one to work at all: > > create or replace function update_rate (integer, integer, integer, > integer, numeric, integer) > returns void > as ' > declare > x_admin_id alias for $1; > x_developer_id alias for $2; > x_client_id alias for $3; > x_project_idalias for $4; > x_rate alias for $5; > x_rate_id alias for $6; > x_meta_id alias for $7; > begin > perform update_lastmod (x_meta_id, x_admin_id); > update rates_tbl set > developer_id= x_developer_id , > client_id = x_client_id , > project_id = x_project_id , > rate= x_rate > where rate_id = x_rate_id ; > return ; > end; > ' language plpgsql; > > > invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216); > ERROR: function update_rate(integer, integer, integer, "unknown", > numeric, integer, integer) does not exist > HINT: No function matches the given name and argument types. You may > need to add explicit type casts. Umm, it looks to me like you're trying to pass 7 parameters to a function that takes 6 in the above. ---(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] alter sequence + subqueries
On Fri, Sep 16, 2005 at 06:36:48PM -0400, jeff sacksteder wrote: > The postgres-specific sql extension 'ALTER SEQUENCE' does not appear to > support subqueries. > > I have inserted some data into a table, including values in the primary key. > I want to reset the associated sequence so that any further records to be > inserted into that table resume numbering correctly. The statement I > attempted was: > > ALTER SEQUENCE foo_id_seq > RESTART WITH (SELECT max(id) FROM foo); > > Is this syntax correct and supported? No, but you could call the setval() function instead ... -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke") ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] plpgsql function not accepting NULL value
Hi, I'm using the postgresql 7.4.7-6sarge1 package from debian stable. I am confused about these two functions, and their behavior when being given NULL values in place of a parameter declared as integer: This one works as expected: create or replace function insert_rate (integer, integer, integer, integer, numeric) returns bigint as ' declare x_admin_id alias for $1; x_developer_id alias for $2; x_client_id alias for $3; x_project_idalias for $4; x_rate alias for $5; x_meta_id int; -- and the return id x_rate_id bigint; begin select into x_meta_id insert_meta (x_admin_id); insert into rates_tbl ( developer_id, client_id, project_id, rate, meta_id ) values ( x_developer_id, x_client_id, x_project_id, x_rate, x_meta_id ); select into x_rate_id currval(\'rates_tbl_rate_id_seq\'); return x_rate_id; end; ' language plpgsql; invoicer=> select insert_rate(1, 1, 1, NULL, '60.00'); insert_rate - 7 (1 row) invoicer=> select * from rates_tbl; rate_id | rate | developer_id | client_id | project_id | meta_id -++--+---++- ...other rows snipped... 7 | 60.00 |1 | 1 || 220 (7 rows) I can't get this one to work at all: create or replace function update_rate (integer, integer, integer, integer, numeric, integer) returns void as ' declare x_admin_id alias for $1; x_developer_id alias for $2; x_client_id alias for $3; x_project_idalias for $4; x_rate alias for $5; x_rate_id alias for $6; x_meta_id alias for $7; begin perform update_lastmod (x_meta_id, x_admin_id); update rates_tbl set developer_id= x_developer_id , client_id = x_client_id , project_id = x_project_id , rate= x_rate where rate_id = x_rate_id ; return ; end; ' language plpgsql; invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216); ERROR: function update_rate(integer, integer, integer, "unknown", numeric, integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I tried adding an explicit cast to "integer" on the NULL parameter, but just got a syntax error @ "NULL" The only notable difference I see between the functions, is that the problem function returns void; I tried changing the return value to 'integer' and changing the 'return;' line to 'return 1;', but there was no change. Can someone please explain to me what is causing the problem? Thanks, Kenneth ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] alter sequence + subqueries
The postgres-specific sql extension 'ALTER SEQUENCE' does not appear to support subqueries. I have inserted some data into a table, including values in the primary key. I want to reset the associated sequence so that any further records to be inserted into that table resume numbering correctly. The statement I attempted was: ALTER SEQUENCE foo_id_seq RESTART WITH (SELECT max(id) FROM foo); Is this syntax correct and supported?
Re: [SQL] showing multiple REFERENCE details of id fields in single
Ferindo Middleton Jr wrote: Thanks Daryl. The query you wrote works perfectly for the results I was trying to get at. I modified it a little because the real thing I needed to be able to see is the course_title and not the the id of the courses: SELECT a.course_title AS class_title, c.course_title AS prerequisite_class_title FROM classes a JOINclass_prerequisite_bindings b ON b.class_id = a.id JOINclasses c ON c.id = b.prerequisite WHERE a.id = ?; I don't quite understand how this query is working completely but maybe I'll understand after studying it a little longer. Thank you for your help. Ferindo No problem at all. In future, you should try to place your responses in the body of the reply, it makes it easier for others to read later. Perhaps if you envision it this way? classes.id <-> class_prerequisite_bindings.id class_prerequisite_bindings.prerequisite <-> classes.id As a side note, your db schema would be cleaner if you removed the unneeded surrogate key id from the class_prerequisite_bindings table, like so: create table class_prerequisite_bindings( class_id INTEGER REFERENCES classes(id), prerequisite INTEGER REFERENCES classes(id), constraint pk_cpb primary key( class_id, prerequisite ) ); This will, for example, prevent a class from having duplicate prerequisite classes. Good Luck! [snipped] -- Daryl "We want great men who, when fortune frowns, will not be discouraged." -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
