Re: [SQL] Postgres MD5 Function
David Durst wrote: Is there anywhere I can get these in binary? Or is my only option to compile Postgres from source?? Depends on the type of binary. E.g. there is a postgresql-contrib-7.3.1-1PGDG.i386.rpm binary available here: ftp://ftp.us.postgresql.org/binary/v7.3.1/RPMS/redhat-7.3/ Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: CR/LF conversion (was: [SQL] import error)
We are supporting Win32 in 7.4, so it is very likely COPY will support carriage returns the next release. --- Tom Lane wrote: > Oliver Vecernik <[EMAIL PROTECTED]> writes: > > It's always the same problem with CR/LF conversions ... > > Ah, you figured it out. (Didn't see your followup right away because of > the changed subject line.) > > > After changing it to just LFs, everthing worked like a charm. Is there > > an elegant way to handle this automatically? > > There are plans to make COPY treat all common styles of newline alike. > We had to wait a release or two after taking the first step, though, > since it's an incompatible change. > > regards, tom lane > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres MD5 Function
Is there anywhere I can get these in binary?
Or is my only option to compile Postgres from source??
> Larry Rosenman wrote:
>> --On Friday, January 31, 2003 01:34:42 -0800 David Durst
>> <[EMAIL PROTECTED]> wrote:
>>> Does there exsist a MD5 Function I can call???
>>
>> look at /contrib/pgcrypto in the source distribution.
>>
>
> Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5
> function:
>
> regression=# select md5('Joe');
> md5
> --
> 3a368818b7341d48660e8dd6c5a77dbe
> (1 row)
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how can i convert a substring to a date?
On Thu, Jan 30, 2003 at 11:03:43PM -0800, joe.guyot wrote: > greetings all! > > > and continually get different errors: > "bad date external representation 'createdate'" > or > "bad timestamp external representation 'createdate'" > > i'm sure this has an obvious solution but i can't seem to find it. > any suggestions are appreciated. Hmm, the parse is telling you it doesn't know how to express the string 'createdate' as a date or timestamp. Why is that? Because you've asked it to. I presume the fragments you quote above are part of a CREATE VIEW statement. You're asking forthe boolean result of comparing the substring expression to the string 'createdate'. What you probably want is: to_date(substr(creat,1,8),'MMDD') AS 'createdate' Here's an example of use: test=# CREATE VIEW quux AS SELECT to_date(substr(creat,1,8),'MMDD') AS "createdate", substr(creat,13) AS "User" FROM baz; CREATE VIEW test=# select * from baz; creat - 20071623XYX (1 row) test=# select * from quux ; createdate | User +-- 2001-11-17 | XYX (1 row) Ross ---(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] returning table from a function
Hmm.. do you mean functions returning sets then http://techdocs.postgresql.org/guides/SetReturningFunctions is for you. regds mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Help with a query for charting
you could keep a table with list of all possible days in a month.
left join that to the results you get from query below this will return NULL
for days where there is no data. NULL could then easily converted to 0 using CASE
or COALESCE.
regds
mallah.
> I'm trying to do a query to count the number of tickets opened on each day of a
>month. It'll
> always be from the 1st to the end of the month. This does it:
>
> SELECT TO_CHAR(DATE_TRUNC('day',date_opened),'DD') AS day, COUNT(*)
>FROM ticket GROUP BY DATE_TRUNC('day', date_opened)
>WHERE ;
>
> But it doesn't give me a zero for the days when no tickets were opened - which I
>really need
> because I want to graph the result.
>
> I could do this in the application code but that doesn't seem right.
>
> Help would be great.
>
> Andrew
>
>
> ---(end of broadcast)--- TIP 4:
>Don't 'kill -9'
> the postmaster
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] help: pgSQL docs and debug
I need to write some longer pgSQL stored procedures. Can you help? 1. I am looking for a reference of pgSQL functions, etc.? (I have all the books, they have just a few, I also looked all over the web). If you can post a link, tia. For example, what does the || do in pg SQL with a string? I am looking at code examples and having a hard time to follow. 2. How do I debug pgSQL stored procedure? Right now I write step by step. Is there a "system.out" in pgSQL? Where does it show? Is there a GUI editor that helps (I use pgADMIN II). Any help appreciated. .V ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to return records from a function
Hello everyone. I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appreciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just can't figure it out. Thanks, John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to return records from a function
Thanks! That's somewhat of what i was looking for. However it seems that what I wanted to do is still not possible. I'm writing a Java application and wanted to use CallableStatement to call a PostgreSQL function. However it seems that the driver does not implement that functionality. I guess I can just use a regular statement calling a function but there seems to be problems doing that as well. Are there any other JDBC drivers for PostgreSQL? Thanks, john - Original Message - From: <[EMAIL PROTECTED]> To: "John Cavacas" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: February 2, 2003 10:52 AM Subject: Re: [SQL] How to return records from a function > Did you look at > http://techdocs.postgresql.org/guides/SetReturningFunctions > ? You need Postgresql 7.3 to do this. > Regards, > Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help: pgSQL docs and debug
Vic Cekvenich wrote: I need to write some longer pgSQL stored procedures. Can you help? 1. I am looking for a reference of pgSQL functions, etc.? (I have all the books, they have just a few, I also looked all over the web). If you can post a link, tia. Start from "Chapter 19. PL/pgSQL - SQL Procedural Language" on Postgresql documentation. I think, you can find there everything you need for pl/pgsql. After you read this, search techdocs.postgresql.org site. Lots of examples could be find on braseiro.net (don't remember spelling) For example, what does the || do in pg SQL with a string? I am looking at code examples and having a hard time to follow. || has nothing to pl/pgsql. It is a part of SQL standard - string concatenation. 2. How do I debug pgSQL stored procedure? Right now I write step by step. Is there a "system.out" in pgSQL? Where does it show? Is there a GUI editor that helps (I use pgADMIN II). Use "RAISE NOTICE ''My name is: %'',some_row.name " and "RAISE EXCEPTION ..." Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how do i create a date from a substring???
joe.guyot wrote: greetings all! [i just became a member] i have a string data from a view that is a packed field. it contains a date, a time and a user's initials. i'm trying to extract the date portion in a pg 7 view. the data originally came from a ms sql 7 table that has since been converted into a pg 7 table. in the ms sql 7 view the date was extracted as follows: convert(timestamp, substring(creat, 1, 8), 120) = 'createdate' where creat is the packed field from the original table and 'createdate' is the extracted date portion. the data would typically look like: 20071623XYX. the result is '2001-11-17'. i've reviewed documentation, on line books and several threads in this and related newsgroups and can't seem to come up with a decent solution. i've tried various combinations of this in a pg 7 view: to_date(substr(creat,1,8),'-MM-DD') = 'createdate' to_date(substring(creat from 1 for 8),'-MM-DD') = 'createdate' to_timestamp(substr(creat,1,8),'-MM-DD') = 'createdate' to_timestamp(substrsting(creat from 1 for 8),'-MM-DD') = 'createdate' and continually get different errors: "bad date external representation 'createdate'" or "bad timestamp external representation 'createdate'" Your substring-date isn't in '-MM-DD' format, but in 'MMDD' Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help with a query for charting
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> I'm trying to do a query to count the number of tickets opened on
> each day of a month. It'll always be from the 1st to the end of
> the month.
> ...
> But it doesn't give me a zero for the days when no tickets were opened
The problem is that SQL has no concept of how many days there are supposed
to be inside of the range you gave it, now does it have a way of easily
determining how many months are in a year. You will have to put that
information into the database: a simple table with a date field and one
row per day should do it. Make sure that you go well beyond any days
you will ever need. For example:
CREATE TABLE mydates (date_opened date);
(Using the same column name allows us to use "USING" instead of "ON"
in our JOIN later on.)
Populate it somehow. Here is a quick and dirty way to add 1000 days:
perl -e \
"{print \"INSERT INTO mydates VALUES('\" . scalar
localtime($^T+(\$x*86400)). \"');\n\"; redo if \$x++<1000}" \
| psql
Watch those escape characters!
Once you have such a table, you will need to join your query to it,
by using a RIGHT OUTER JOIN (RIGHT OUTER as we are listing the
important table first, then making sure that we have at least one
row from the second, or "right" table). We also need to wrap the
query for the first table inside of a subselect to allow us to use
the GROUP BY with a JOIN. The date specification is only needed on
the second table (mydates), although you could add it to the first
as well if you wish. The TO_CHAR has been moved to the "outer level",
so we can simply join on the DATE_TRUNC'ed column. Finally, a COALESCE
on the count is added, in order to generate the wanted zeroes:
SELECT TO_CHAR(DATE_TRUNC('day',T2.date_opened), 'DD') AS "day",
COALESCE(T1.mycount,0) AS "count"
FROM
(SELECT date_opened, COUNT(*) AS mycount
FROM ticket GROUP BY date_opened) AS T1
RIGHT OUTER JOIN
(SELECT DISTINCT date_opened
FROM mydates
WHERE date_opened BETWEEN '23-Jan-2003' AND '26-Jan-2003') AS T2
USING (date_opened)
ORDER BY "day" ASC;
The DISTINCT is not strictly needed, but is a safeguard in case the
mydates table has more than one entry with the same date.
Hope that helps.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200302021403
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+PX6rvJuQZxSWSsgRAqAxAKC/NwhBKTavlNXYkTmsy7DMcxeLPwCgnP4K
y2RTdNiyQv+V29prKmo1yMw=
=bBpJ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] COPY use in function with variable file name
On Wed, 2003-01-29 at 08:05, Sondaar Roelof wrote:
> Hello,
>
> I can't figure out how to make this work, or is not possible?
>
> In a function i would like to read a file.
> The file name is determined by a value from a table.
> However the COPY statement does not to accept this?
> I tried various forms of adding (single)-quotes but no luck.
>
> Anyone any ideas?
>
> Function:
> CREATE FUNCTION dnsdhcp_dns_raw()
> /* Fill table dns_raw with dns data */
> RETURNS integer AS '
> DECLARE
> r RECORD;
> ntw TEXT;
> BEGIN
> /* Do for all domain names */
> FOR r IN SELECT domain FROM network
> WHERE position(''n'' IN use) > 0 and ipaddress != ''127.0.0.0/24''
> LOOP
> ntw := ''/tmp/db.'' || r.domain;
> DELETE FROM dns_raw; /* Clear table */
> RAISE NOTICE ''Network: %'', ntw;
> COPY dns_raw FROM ntw DELIMITERS ''~''; /* Get the data */
Since ntw has variable content, you need to do an EXECUTE with the
command in a text string:
EXECUTE ''COPY dns_raw FROM '' || ntw || '' DELIMITERS ='';
(I hope that is the right number of quotes!)
> END LOOP;
> RETURN 0;
> END;'
> LANGUAGE 'plpgsql';
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Love not the world, neither the things that are in the
world. If any man love the world, the love of the
Father is not in him...And the world passeth away, and
the lust thereof; but he that doeth the will of God
abideth for ever." I John 2:15,17
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL]
Can some one please confirm( with a plpgsql function example please ) a postgres "7.2" function that can return multiple rows to the client. I've gone through the docs and can't find anything to support this -it seems you can only have one return value or null. Yes i'm aware this it is possible in 7.3 - can someone please confirm its not possible in 7.2 or provide me with an example of how to go about it. Thank you.
Re: [SQL] PL/pgSQL question
Jim, > Yes i'm aware this it is possible in 7.3 - can someone please confirm its > not possible in 7.2 or provide me with an example of how to go about it. It is sort of possible in 7.2, as a function can return a cursor to an interface language capable of handling cursors. However, implementation is quite cumbersome, and I won't try to detail it here, particularly since that language and method have been obsolesced and would force you to re-write your code when you do upgrade. If this feature is important to you, I strongly recommend that you upgrade to 7.3.1 now. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]
On Sun, 2003-02-02 at 21:47, mail.luckydigital.com wrote: > Can some one please confirm( with a plpgsql function example please ) > a postgres "7.2" function that can return multiple rows to the client. > > I've gone through the docs and can't find anything to support this -it > seems you can only have one return value or null. > > Yes i'm aware this it is possible in 7.3 - can someone please confirm > its not possible in 7.2 or provide me with an example of how to go > about it. Not possible in 7.2 PL/pgSQL -- Oliver Elphick <[EMAIL PROTECTED]> LFIX Limited ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] extracting constraints from schema
Agnieszka Rutkowska wrote: Hi, I was just wondering whether you happen to know how to extract what the table and column constraints are using jdbc? The API provides methods for extracting meta data on the table names, column names, primary and foreigh keys as well as column types and sizes. What about extracting constraints such as salary real CHECK (salary >= 1) ? It is possible to obtain the column name 'salary' as well as its types as being 'real' but how about the CHECK condition?? Can it be done using jdbc? Thanks for your time and help Agnes Did you try to extract any information from system table pg_constraint? contype='C' is a CHECK condition. I think you can parse some additional information from column consrc. Description of all columns in this table is in "Chapter 3. System Catalogs 10. pg_constraint" at Postgresql documentation. 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
[SQL] window version of psql of postgreSQL v7.3.1
Hi, all Where can I get a window version of psql of postgreSQL v7.3.1? Thank you very much! Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] TEMP tables
hi, I have a plpgsql procedure that needs to create a temporary table, use it as a stack internally, and then disgard it when the procedure exits. This works great if I only call this procedure ONCE per session. Calling it a second time within the same session produces: WARNING: Error occurred while executing PL/pgSQL function render WARNING: line 8 at SQL statement ERROR: Relation 'stack' already exists (line 8 is the CREATE TEMP TABLE statement) But when I add DROP TABLE stack at the end (but within) the procedure, while it works okay the first time around, it produces the following error after first execution: WARNING: Error occurred while executing PL/pgSQL function render WARNING: line 9 at SQL statement ERROR: pg_class_aclcheck: relation 20900 not found (line 9 is a SELECT * FROM stack statement) It seems that after a table is created, dropped and then created again it's not recognised. When i do this outside of a procedure it works fine (i can create/drop a temp table as many times as i want). Is there something magical that procedures do when a table is created inside of them? (like cache the relation id, and when you swap it underneath them, they panic?) Just thoughts. Honestly, I have no clue :-) Which is why I am asking :-) What are the recommendations or solutions on using temporary tables inside functions on a per call basis? thanks a lot, - lex -- Lex Berezhny <[EMAIL PROTECTED]> ---(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] TEMP tables
You need to use EXECUTE. See the FAQ, item 4.26. --- Lex Berezhny wrote: > hi, > > I have a plpgsql procedure that needs to create a temporary table, use > it as a stack internally, and then disgard it when the procedure exits. > > This works great if I only call this procedure ONCE per session. Calling > it a second time within the same session produces: > > WARNING: Error occurred while executing PL/pgSQL function render > WARNING: line 8 at SQL statement > ERROR: Relation 'stack' already exists > > (line 8 is the CREATE TEMP TABLE statement) > > But when I add DROP TABLE stack at the end (but within) the procedure, > while it works okay the first time around, it produces the following > error after first execution: > > WARNING: Error occurred while executing PL/pgSQL function render > WARNING: line 9 at SQL statement > ERROR: pg_class_aclcheck: relation 20900 not found > > (line 9 is a SELECT * FROM stack statement) > > It seems that after a table is created, dropped and then created again > it's not recognised. > > When i do this outside of a procedure it works fine (i can create/drop a > temp table as many times as i want). Is there something magical that > procedures do when a table is created inside of them? (like cache the > relation id, and when you swap it underneath them, they panic?) Just > thoughts. Honestly, I have no clue :-) Which is why I am asking :-) > > What are the recommendations or solutions on using temporary tables > inside functions on a per call basis? > > thanks a lot, > > - lex > > -- > Lex Berezhny <[EMAIL PROTECTED]> > > > ---(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 > -- 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 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
