Re: [SQL] count record in plpgsql
how can i know the count of record in plpgsql.example if i'm query with query komponent from delphi i will know the record count of record.but how with plpgsql. any ideas? Is it what you need: GET DIAGNOSTICS n = ROW_COUNT; after executing query ? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] count record in plpgsql
Nurdin wrote: how can i know the count of record in plpgsql.example if i'm query with query komponent from delphi i will know the record count of record.but how with plpgsql. GET DIAGNOSTICS integer_var = ROW_COUNT; See the manual for details (plpgsql section, "Obtaining the result status"). HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] version 7.4 autocommit removed
The Release Notes on 7.4 state Observe the following incompatibilities: The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own autocommit behavior, so autocommit was removed from the server and added to individual client APIs as appropriate. So I tried set autocommit to off after connecting to my database and expected a session-wide autocommit off. It failed saying SET AUTOCOMMIT TO OFF is no longer supported Could someone please enlighten me how this first sentence should be understood: The server-side autocommit setting was removed and reimplemented in client applications and languages. Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] INDEX and LIKE
Hello, I need to be able to perform wildcard searches on a VARCHAR(100) column of the form SELECT * FROM item WHERE serial_no LIKE '%12345678%' Would an index on serial_no do anything at all for this search? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(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] INDEX and LIKE
On Mon, 29 Nov 2004 12:19:12 +, T E Schmitz <[EMAIL PROTECTED]> wrote: Hello, I need to be able to perform wildcard searches on a VARCHAR(100) column of the form SELECT * FROM item WHERE serial_no LIKE '%12345678%' Would an index on serial_no do anything at all for this search? No, It wouldn't. If you use "%" for the beginning of you search pattern, a seqential search is performed, ignoring the index wich use the beginning of the string in order to create it's B-tree. -- Downloading signature ... 99% *CRC FAILED* signature aborted ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Crystal Report + Bytea field
Hai friends, I stored image in binay format at a field with type bytea. Now i have to display the image(Stored in the bytea field) in a crystal report. is this possible. How? Pls Reply Sreejith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] stored procedures in postgresql user plpgsql
I know that there are not stored procedures like that in oracle. I know that you have to create functions. I have tried to create a simple function to select data from a table. Could you all please correct me Thanks CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS ' BEGIN RETURN select username from masteraccount where atype=$1; END; ' LANGUAGE 'plpgsql'; To call the function I used Select retrive_atype(); Please help Robert Stewart Network Eng Commonwealth Office of Technology Finance and Administration Cabinet 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED]
Re: [SQL] stored procedures in postgresql user plpgsql
On Mon, Nov 29, 2004 at 01:37:42PM -0500, [EMAIL PROTECTED] wrote:
> I have tried to create a simple function to select data from a table. Could
> you all please correct me
It would be helpful if you told us what you want to happen and what
actually does happen. Without that information we have to guess
at your intentions.
> CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS '
> BEGIN
> RETURN select username from masteraccount where atype=$1;
> END;
> ' LANGUAGE 'plpgsql';
>
> To call the function I used
>
> Select retrive_atype();
You've defined the function to take a VARCHAR argument but you
called it without an argument. Also, you've defined it to return
a single VARCHAR value, but if the query could return multiple
values then the function should return SETOF VARCHAR. Maybe this
is closer to what you need:
CREATE FUNCTION retrive_atype(VARCHAR) RETURNS SETOF VARCHAR AS '
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT username FROM masteraccount WHERE atype = $1 LOOP
RETURN NEXT rec.username;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
You'd call the function like this:
SELECT * FROM retrive_atype('some-atype-value');
If that's not what you're looking for then please provide more details.
BTW, is "retrive" supposed to be "retrieve"?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] PG7.4.5: query not using index on date column
Sorry for the delay in getting back on this thread, painting my apartment meant no internet over this thanksgiving break... :) How many rows in the table altogether? A rough guess is a few million based on the estimated seqscan cost. That would mean that this query is retrieving about 10% of the table, which is a large enough fraction that the planner will probably think a seqscan is best. It may be right. If you do "set enable_seqscan = off", how does the EXPLAIN ANALYZE output change? Right now it's a hair under 800k rows. With enable_seqscan = off, I get this as my analyze results: QUERY PLAN - Sort (cost=361676.23..361676.33 rows=38 width=226) (actual time=46076.756..46076.924 rows=69 loops=1) Sort Key: count -> Subquery Scan aggs (cost=361674.10..361675.24 rows=38 width=226) (actual time=46068.621..46076.159 rows=69 loops=1) -> HashAggregate (cost=361674.10..361674.86 rows=38 width=54) (actual time=46068.596..46075.170 rows=69 loops=1) -> Index Scan using received_date_idx on messages (cost=0.00..349968.44 rows=585283 width=54) (actual time=20.988..15020.821 rows=589543 loops=1) Index Cond: ((received_date >= '2004-11-01'::date) AND (received_date <= '2004-11-30'::date)) Total runtime: 46091.315 ms Quite a bit higher with these estimates. Using your 10% of the table guess above, I did a breakdown by date and got these results: [EMAIL PROTECTED]:geekisp=>select received_date, count(received_date) from spamreport.messages group by received_date order by received_date asc; received_date | count ---+--- 2004-10-20| 7592 2004-10-21| 19699 2004-10-22| 17311 2004-10-23| 16730 2004-10-24| 18249 2004-10-25| 16718 2004-10-26| 16951 2004-10-27| 19818 2004-10-28| 19580 2004-10-29| 17610 2004-10-30| 16210 2004-10-31| 20468 2004-11-01| 12337 2004-11-02| 9012 2004-11-03| 20871 2004-11-04| 20103 2004-11-05| 18807 2004-11-06| 20131 2004-11-07| 22291 2004-11-08| 23041 2004-11-09| 20254 2004-11-10| 17810 2004-11-11| 21091 2004-11-12| 21976 2004-11-13| 18824 2004-11-14| 20543 2004-11-15| 18829 2004-11-16| 24248 2004-11-17| 18093 2004-11-18| 25675 2004-11-19| 27084 2004-11-20| 22362 2004-11-21| 25187 2004-11-22| 26451 2004-11-23| 26016 2004-11-24| 23147 2004-11-25| 25785 2004-11-26| 20584 2004-11-27| 25615 2004-11-28| 6931 2004-11-29| 6549 (41 rows) So it looks like an aggregation of 2 weeks worth of data is more than 10%, so its aligned with what you were saying. That also jives, since when I re-enable seq_scan and do the report for one or two day's of data - it uses the index (previously it did not - perhaps there was not enough data). If it's not right, you may want to try to adjust random_page_cost and/or effective_cache_size so that the planner's estimated costs are more in line with reality. Beware of making such adjustments on the basis of only one test case, though. I'll look in the manual and try playing with these options, thanks for your suggestions! In the end, I'm guessing that if I need to retrieve a few hundred thousand rows from disk, faster disks might make the biggest impact on performance. Thanks for your help! -- Dave Steinberg http://www.geekisp.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] create stored procedure from temporary table
i was wondering, how to create dynamic table like temporary table but create from function and the result from function toocos i want count same calculation from other table migh be a multiple table so i need looping and join table... thnax nurdin
Re: [SQL] Way to stop recursion?
Jonathan Knopp wrote: Sorry, I should have mentioned that there is a lot more to the design that makes this replication necessary, including another two levels to the tree plus the ability to have orphaned children. My first thought was "Dude, use a VIEW" In database design, the SPOT principle applies. *Always* enforce a Single Point Of Truth. If that doesn't seem to be possible, rethink how the data is used and look at how to ensure that there is only ONE authoritative storeage for each piece of transactional data. (Yes, sometimes we get away from this with OLAP installations but the data is not generally being updated there.) In this case, I would create a view (with appropriate rules) which would automatically populate the common fields from the parent if it exists. The issue should not be one of storage but of presentation. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] grouping a many to many relation set
Hi, I am having a problem grouping a many to many relationship with payments and receipts, where a payment can be for multiple receipts, and a receipt can have multiple payments. I got a list of records that are involved in such relations, but now I don't know how to group them so that all payments and rececipts belonging to the same group are properly grouped. Here's the list: bankbookdetid | receiptid ---+--- 147 |25 157 |25 157 | 622 321 | 100 332 | 101 332 | 100 2156 | 573 2156 | 574 2156 | 575 1710 | 575 1710 | 576 I have already grouped them according to the way they should be grouped: bankbook payments and receipt amounts that are part of the same transaction (they are a subset of a large set of payments and receipts, most 1-1, 1-n and n-1, which are solved relatively easy). As you can see there are a few records that interconnect the payments and receipts: bankbookdetid | receiptid ---+--- 157 |25 332 | 100 2156 | 575 1710 | 575 I tried now for some time how a SQL statement could give a set grouped as you can see above, but I just don't seem to see it. Is there anyone around over here that had a similar situation and has found a solution? Should I try to do this in PL/SQL? Is there a solution for the problem anyway? -johan smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] create stored procedure from temporary table
Oops! [EMAIL PROTECTED] ("Nurdin") was seen spray-painting on a wall:
> i was wondering, how to create dynamic table like temporary table
> but create from function and the result from function toocos i
> want count same calculation from other table migh be a multiple
> table so i need looping and join table...
Well, you can define a temporary table inside a stored procedure using
EXECUTE, as long as all of the operations that touch that table are
kept inside dynamic SQL invoked using EXECUTE...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/oses.html
"Bonus! The lack of multitasking is one of the most important reasons
why DOS destroyed Unix in the marketplace." -- Scott Nudds
---(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] TEXT::CIDR/INET::CIDR output confusion
Added to TODO list: * Prevent INET cast to CIDR from droping netmask, SELECT '1.1.1.1'::inet::cidr It is probably related to the TODO item above it: * Prevent INET cast to CIDR if the unmasked bits are not zero, or zero the bits I think the original code thought CIDR and INET where identical types that could be cast with no changes but we are finding that was incorrect. --- Alexander M. Pravking wrote: > It looks a bit strange that CIDR output depends on datatype it has been > casted from: > > fduch=# SELECT '1.1.1.1'::cidr; > cidr > > 1.1.1.1/32 > (1 row) > > fduch=# SELECT '1.1.1.1'::inet::cidr; > cidr > - > 1.1.1.1 > (1 row) > > > However these two seem to be 'equal' in terms of backend: > > fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::inet; > ?column? > -- > t > (1 row) > > fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::cidr; > ?column? > -- > t > (1 row) > > > I'm just curious how can it even be... > > fduch=# SELECT version(); > version > > PostgreSQL 7.4.5 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 > [FreeBSD] 20040728 > > > -- > Fduch M. Pravking > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] stored procedures in postgresql user plpgsql
Please Cc the mailing list when you respond -- that way others can participate in and learn from the discussion. Also, if you have a new question then please start a new thread with a relevant Subject header. On Mon, Nov 29, 2004 at 10:12:59PM -0500, [EMAIL PROTECTED] wrote: > Thanks for the help > > That is what I needed > > Do you have any examples of insert statements for multiple variable > > I'm trying to create a web page that would call these functions See the documentation for the INSERT statement: http://www.postgresql.org/docs/7.4/static/sql-insert.html If you're having trouble with something, then please describe what you're trying to do and the difficulties you're having. It's helpful if you post the SQL statements you're executing along with a description of what you want to happen and what actually does happen. For the latter, including the output from a psql session can be useful. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] TEXT::CIDR/INET::CIDR output confusion
Bruce Momjian <[EMAIL PROTECTED]> writes: > I think the original code thought CIDR and INET where identical types > that could be cast with no changes but we are finding that was incorrect. That's what it seems to boil down to. We need to rethink the separation between those datatypes. At the very least, the casts between them cannot both be code-less binary-compatibility casts. 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
