[SQL] Passing composite values to functions
Hi, I'm having trouble passing a composite variable to a function. I have a composite as follows: declare userRecord dnaUsers%ROWTYPE; and I manipulate it like this: select into userRecord * from dnaUsers where etc; userRecord.userid := 'jatinder'; --etc I would like to then insert this record into the table (kind of like this): insert into dnaUsers select userRecord; insert into dnaUsers select userRecord.*; insert into dnaUsers values (userRecord); insert into dnaUsers values (userRecord.*); All of the above generate various errors relating to "userRecord" not being a table? I can't find any examples of this in the archives or the documentation. Is this even possible? I also, haven't found a way to pass my "userRecord" to a function, eg: create or replace function isUserActive (dnaUsers) returns boolean as ' do something with $1; return true; ' language plpgsql; The problem here is that I can't pass userRecord to the new function: isActive := isUserActive (userRecord); Again, the errors appear to be about "userRecord" not being a table? So, is it even possible to use composite records in this manner? All of the examples I've seen, seem to be about passing a row of a direct select statement as a composite argument to a function, and not about passing a locally declared composite variable. Can anyone help correct my understanding of what I can and can't do with composites? Thanks, --Jatinder
Re: [SQL] 7.4 - FK constraint performance
> In this precise example, could you not:
> 1. Check index for value
> 2. If found, seq-scan
>
> Of course that's only going to be a sensible thing to do if you're expecting
> one of two results:
> 1. Value not there
> 2. Lengthy seq-scan if it is there
Most of the queries are going to be for the other values (in which case
you've wasted an index scan) which is minor, but in the event there is a
single 239 you're still taking a big hit.
That is an awful lot of work to handle the non-existant case only.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] 7.4 - FK constraint performance
On Thu, 2004-02-12 at 23:25, Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > Statistics say there are 10 values. Statistics list the 10 most common
> > values (all of them). Given this, would it not be reasonable to assume
> > that 239 is a recent addition (if there at all) to the table and not
> > very common?
>
> We don't know that it's 239 when we make the plan. In order to know
> that, we'd have to abandon caching of RI check query plans and re-plan
> for each row. That strikes me as inevitably a losing proposition.
Right, sorry, I forgot this was out of a fk trigger.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] 7.4 - FK constraint performance
On Thu, 2004-02-12 at 20:10, Tom Lane wrote:
> ow <[EMAIL PROTECTED]> writes:
> > Sounds pretty bad for my case. Any way to avoid the 10% scan?
>
> Can't see how we optimize your case without pessimizing more-common cases.
> Sorry.
Statistics say there are 10 values. Statistics list the 10 most common
values (all of them). Given this, would it not be reasonable to assume
that 239 is a recent addition (if there at all) to the table and not
very common?
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
[SQL] bytea or blobs?
Hi, what I am trying to do is to be able to store images in my database. What I wanted to know is this: 1. Would it be better to have the image field type as a bytea or a blob? I have heard it mentioned that bytea would be better as doing data dumps would also insure that the image was saved as well! 2. Would it be better to make reference to mp3 files (i.e. storing the address of the image /images/*.jpg) or is it feasible to store the mp3 in the database as bytea or blobs as well? many thanks in adavance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] determining how many products are in how many categories
You might be better off with something more like this: SELECT COUNT(P.*) AS p_count, P.p_product_category_id FROM p_product P WHERE P.p_product_category_id IN (SELECT DISTINCT id FROM p_product_category) GROUP BY P.p_product_category_id; obviously tailored to YOUR schema... not mine... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 "David Garamond" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > # product table (simplified): > create table p ( >id char(22) not null primary key, >name text, >desc text > ); > > # product category table (simpl.): > create table pc ( >id char(22) not null primary key, >name text, >desc text > ); > > # table that maps products into categories: > create table p_pc ( >id char(22) not null primary key, >pid char(22) not null references p(id), >pcid char(22) not null references pc(id) > ); > create index i_ppc_pid on p_pc(pid); > create index i_ppc_pcid on p_pc(pcid); > create unique index i_ppc_pid_pcid on p_pc(pid, pcid); > > There are about 50k rows in p, 40k in pc, and 140k in p_pc. Most > products are only placed in 1 category, some in 2, fewer in 3, and fewer > stills in 4, 5, 6 categories. > > I want to know how many products are placed in 1 category, how many in > 2, and so on. Here's my query: > > select count(pid),num_cat from ( >select pid,count(cid) as num_cat >from ppc group by pid) as f > group by num_cat; > > A sample output (4000 products are categorized in 5 different places, > 4998 in 4, and so on): > > count | num_cat > ---+- >4000 | 5 >4998 | 4 >7502 | 3 > 10001 | 2 > 17499 | 1 > (5 rows) > > However, this query is taking about 2.5 minutes. Any idea on how to make > it faster? > > -- > dave > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Unique Constraint with foreign Key
Pleas also note that the referenced column in the foreign table either needs
to be the PRIMARY KEY or have a unique constraint on it or maybe it just
requires an index on it -- I'm not sure but I discovered that if the column
in the foreign table (containing the REFERENCED key...) is NOT the primary
key column -- the REFERENCES a(x) will faill unless a.x is specified as
'UNIQUE' -- as in the following example:
create table a (
y integer not null primary key default nextval('nexta_seq'),
x varchar not null UNIQUE
);
create table b (
z integer not null PRIMARY KEY default nextval('nextbz_seq'),
x varchar NOT NULL REFERENCES a(x),
);
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
"Bruno Wolff III" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, Feb 10, 2004 at 12:03:36 -0500,
> [EMAIL PROTECTED] wrote:
> > Hi all i am using postgres and torque
> > I have a column x in Table A of type varchar and NOT NULL. I want to
> > impose a unique key constraint on that table and column y is the primary
> > key.
> > I have another Table B with column x. can i make this column x as a
> > foreign key on column x in table A.
>
> Yes. The reference will need to specify column x, since the primary key
> column (y) will be the default.
>
> > or Can i have varchar types of size 50 as primary keys in Postgres.
>
> Yes. But unless the 50 character limit comes from a business rule, you
> might want to use the type 'text'.
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function
Hi All, This function of mine gives a Parse Error: CREATE or replace FUNCTION Proc_ConferenceSummary(date,date,int,int,int) RETURNS SETOF tbl_error_master AS ' declare li_CallManagerId int; begin select * FROM tbl_error_master; end; ' LANGUAGE SQL; psql:Proc_ConferenceSummary.sql:9: ERROR: parser: parse error at or near "int" at character 31 I don't see any Syntax Error. Can you please let me know what can be the issue? Regards, Sumita ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Unable to convert date to tm
Hi I have a column x in a table of type date and i have an index on x runing the query "SELECT * FROM table WHERE x >= '2004-01-01" i get this: Unable to convert date to tm where using a cast like this: "SELECT * FROM table WHERE x >='2004-01-01::timestamp" result is ok but it doesn't use index. Thanks in advance. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ORDER BY TIMESTAMP_column ASC, NULL first
Hi! I read posts telling me that NULL values are considered greater than non-null values. Fine. Is there a way to explicitly reverse this? I have the situation where a table holds IP-addresses. The table has column of type timestamp, called assignedAt, which tells when the address was assigned to a computer in our network. What I'd like to do, is to get an IP-address that hasn't yet been assigned to anyone, or the one with the smallest assignedAt (most likely to not be in use). CREATE TABLE IPv4Address ( id SERIAL PRIMARY KEY, address INET NOT NULL UNIQUE, assignedAt TIMESTAMP ); The query to use would be, SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt ASC LIMIT 1; with the exception that this returns rows with NULL at the end, instead of at the beginning which is what I'd like. How do achieve this with one query? I'm using Postgres 7.4. And oh, I'm not on the list so please cc my adress in any replies! Thanks in advance, Fredrik Wendt ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] returning multiple resultset?
Hello all, I am fairly new to PostgreSQL and I am right now converting a database from SQL server. SQL server can return multiple select statements in the function body. Is there any way to return a function with two different resultsets? select * from table1; select * from table2; Thanks for your time and help ---(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
[SQL] max timestamp
i'm trying to get the max time stamp, from each day, of a range of dates, not just the max time stamp for the complete range dates but for each day. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] umlimited arguments on function
Is it possible to write a function (in sql or plpgsql) that allows for an unknown amount of arguments? Something similar to C's printf() function. ~ chris
[SQL] Trace for postgreSQL
Hi, can anyone tell me whether there is a trace facility (application) available for postgreSQL version 7.4.1 which will enable me to see all incoming requests being made to the database (ala SQL Server)? many thanks in advance. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] writing a dynamic sql
Dear friends,I am having an lengthy SQL, which will be called every conditions of if...else statement. I tried with passing that via a string and execute it.But I got error. v_sql := 'INSERT INTO activities( activity_id, parent_activity_id, activity_type_id, subject, description, category_id, priority_id, activity_start_time, activity_end_time,) VALUES ( NEXTVAL(\'seq_activities\'), rec_recurrence.activity_id, rec_activity.activity_type_id, rec_activity.subject, rec_activity.description, rec_activity.category_id, rec_activity.priority_id, rec_activity.activity_start_time, rec_activity.activity_end_time);'; execute v_sql;I got an error saying that the record variable which I declared earlier isnot having reference (or it does not exists).Any suggestion to pack this inside a dynamic function and call it insidemany if conditions.Pls shed some light.Regardskumar
Re: [SQL] returning multiple resultset?
[EMAIL PROTECTED] (ismail) writes: > I am fairly new to PostgreSQL and I am right now converting a database > from SQL server. SQL server can return multiple select statements in > the function body. Is there any way to return a function with two > different resultsets? You can fake it in some cases by returning opened cursors. See the plpgsql docs' discussion of working with cursors. Of course this will only work for resultsets that you can specify as a SQL query. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
Dnia 2004-02-12 11:06, Użytkownik Fredrik Wendt napisał: Hi! I read posts telling me that NULL values are considered greater than non-null values. Fine. Is there a way to explicitly reverse this? Sure, choose combination you need: order by some_value is null asc/desc, some_value asc/desc Regards, Tomasz Myrta ---(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] max timestamp
On Tue, 10 Feb 2004, Michael Sterling wrote: > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. Maybe something like? SELECT CAST(timestampcol AS DATE), max(timestampcol) FROM thetable GROUP BY CAST(timestampcol AS DATE); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
On Thu, 12 Feb 2004, Fredrik Wendt wrote: > I read posts telling me that NULL values are considered greater than > non-null values. Fine. Is there a way to explicitly reverse this? Not directly, but I think ORDER BY assignedAt IS NOT NULL, assignedAt ASC will give the ordering you want at the cost of probably not using an index on assignedAt. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Unable to convert date to tm
Ramiro Arenas R <[EMAIL PROTECTED]> writes: > I have a column x in a table of type date and i have an index on x > runing the query "SELECT * FROM table WHERE x >= '2004-01-01" > i get this: > Unable to convert date to tm AFAICT, that error message hasn't existed in PG since 7.1. If you are running 7.1.* or older, I'd suggest an update. The particular issue here seems to be that 7.1 does not contain a workaround for broken mktime() library routines that reject dates before 1970. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] max timestamp
When grilled further on (10 Feb 2004 10:14:04 -0800), [EMAIL PROTECTED] (Michael Sterling) confessed: > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. > Well, one gross and ugly way is: SELECT MAX( "when" ) FROM readings, (SELECT DATE_TRUNC( 'day', "when" ) AS period FROM readings GROUP BY period) AS p WHERE DATE_TRUNC( 'day', "when" ) = p.period GROUP BY p.period; given the table readings looks something like: CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY ); I'm curious to see other, 'better' solutions. Cheers, Rob -- 12:52:57 up 20:36, 2 users, load average: 2.08, 2.17, 2.18 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
Re: [SQL] max timestamp
Dnia 2004-02-10 19:14, Użytkownik Michael Sterling napisał:
i'm trying to get the max time stamp, from each day, of a range of
dates, not just the max time stamp for the complete range dates but
for each day.
select max(some_time)
group by some_time::date
or
select max(some_time)
group by date_trunc('day',some_time)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] writing a dynamic sql
Dnia 2004-02-10 10:48, Użytkownik Senthil Kumar S napisał: Dear friends, I am having an lengthy SQL, which will be called every conditions of if...else statement. I tried with passing that via a string and execute it. But I got error. v_sql := 'INSERT INTO activities( activity_id, parent_activity_id, activity_type_id, subject, description, category_id, priority_id, activity_start_time, activity_end_time, ) VALUES ( NEXTVAL(\'seq_activities\'), rec_recurrence.activity_id, rec_activity.activity_type_id, rec_activity.subject, rec_activity.description, rec_activity.category_id, rec_activity.priority_id, rec_activity.activity_start_time, rec_activity.activity_end_time );'; execute v_sql; I got an error saying that the record variable which I declared earlier is not having reference (or it does not exists). Any suggestion to pack this inside a dynamic function and call it inside many if conditions. Your v_sql variable *must* contain static text only. It can't have any references to variables. Here is little example how your query should look like: ... VALUES ( NEXTVAL(\'seq_activities\'), '' || rec_recurrence.activity_id::text || '','' || rec_activity.activity_type_id::text ... After this v_sql will be: VALUES ( NEXTVAL(\'seq_activities\'),1,2,... Regards, Tomasz Myrta ---(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] max timestamp
On Tue, Feb 10, 2004 at 10:14:04 -0800, Michael Sterling <[EMAIL PROTECTED]> wrote: > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. SELECT DISTINCT ON can probably do what you want. Something like: SELECT DISTINCT ON (timestamp::DATE) * FROM table ORDER BY timestamp DESC; This won't produce any records for days where there are no timestamps. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Function
On Thu, 12 Feb 2004, Sumita Biswas (sbiswas) wrote: > Hi All, > > This function of mine gives a Parse Error: > > CREATE or replace FUNCTION Proc_ConferenceSummary(date,date,int,int,int) > RETURNS SETOF tbl_error_master AS > ' > declare > li_CallManagerId int; > begin > select * FROM tbl_error_master; > end; > ' > LANGUAGE SQL; I think you want plpgsql as the language. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote: > Hi! > > I read posts telling me that NULL values are considered greater than > non-null values. Fine. Is there a way to explicitly reverse this? ORDER BY column IS NOT NULL, column ASC; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] bytea or blobs?
I'm in the same situation. I struggled with it for days considering various implementation issues for my application and what I hoped to achieve. I opted to use bytea but getting the data in and out is going to be interesting . have to use encode/decode. However I don't have to worry about making triggers and associated to invoke lo_import/export/unlink. There is no true BLOB in Postgresql. There is use of OID column and lo_import et al and there is bytea. My objective to assure integrity of the database lead me to want bytea. I am not by any means done with the situation and would welcome discussion. I'm using Perl for my application language. On Wed, 11 Feb 2004, beyaNet Consultancy wrote: > Hi, > what I am trying to do is to be able to store images in my database. > What I wanted to know is this: > > 1. Would it be better to have the image field type as a bytea or a > blob? I have heard it mentioned that bytea would be better as doing > data dumps would also insure that the image was saved as well! > > 2. Would it be better to make reference to mp3 files (i.e. storing the > address of the image /images/*.jpg) or is it feasible to store the mp3 > in the database as bytea or blobs as well? > > many thanks in adavance > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Passing composite values to functions
"Jatinder Sangha" <[EMAIL PROTECTED]> writes: > I'm having trouble passing a composite variable to a function. AFAIK, plpgsql doesn't support this --- it doesn't have any way to pass a row or record variable as a parameter to the main executor. Possibly this will get fixed someday; I don't know of anyone working on it now though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] umlimited arguments on function
On Monday 09 February 2004 14:49, Christian Hergert wrote:
> Is it possible to write a function (in sql or plpgsql) that allows for
> an unknown amount of arguments? Something similar to C's printf()
> function.
>
AFAIK no, but I know of two possible work arounds... first is to write
multiple copies of the function to accept the different number of argument
combinations you need. the other thing i've done in the past is to create
the function as accepting a array as one of its inputs and then handle the
array structure as needed. (bonus points for doing the second method in 7.3
where its much more challenging.) HTH,
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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
[SQL] nextval problem
I'm new to postgresql and I'm having problems with the 'nextval' function
(or whatever it is called in postgresql).
I created a table (called ADDRESS) using 'pgadmin' which has a serial field
(called addressid). When I tried the sql statement:
select nextval('public."ADDRESS_addressid_seq"')
everything seemed to be working fine.
I then used the create table statement (displayed in pgadmin when the table
'ADDRESS' is selected) to create the same table (of course after first
dropping 'ADDRESS'). When I try the same select-statement I suddenly got an
error message saying:
ERROR: Relation "public"."ADDRESS_addressid_seq" does not exist
Can anybody help me with this problem?
---(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
