Re: [SQL] Compiling pl/pgsql functions
> AFAIK there's not much you can do for obfuscation of pl functions right
> now since someone will be able to see the src text in pg_proc. However,
> are you allowing people that you don't want to see the code access to
> write arbitrary sql to the database?
This is another one of those items where it would be nice if users
didn't need access to read the system tables, but instead could rely on
the information schema (with extensions) to see what they own or have
access to use -- but nothing else.
Sometimes HR gets paranoid about billing seeing their business logic, or
lack thereof, but accounting needs to use both sets of information to do
their work.
Otherwise, having each group relegated to their own schema with
semi-public views is a nice way to pass information from department to
department for small companies. Sure beats the spreadsheets on the
central filer approach.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] Date / interval question
I'm looking for a way, within SQL, given a starting date and an ending date, to get back the number of months between the start and end date. If I "SELECT end_date - start_date", I get back an interval in days; I need months. Thanks for any suggestions, Brian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL / Join Help
First off, thanks for taking the time to read this and try to assist. I have 3 tables: assets,polls,aps -Assets is a master list of all of the wireless assets in our environment. -Polls is an ongoing table containing data from a script that logs into 140 AP's every hour on the hour and retrives the clients associated at that time. -Aps is a master list of all of the aps in our environment. So, I am creating a report that will step through each of the assets and find the most recent entry in the polls table, which is basically the last time that the client was associated. (I understand that if a client associates from :15-:45 every hour that this script will never see it) I am doing all of this in php. I used to have two separate queries to do this (I've removed a lot of the other SQL and php syntax as to not as to not garble or distract)... #Get the list of assets $assetlist = SELECT * FROM assets #Start a while loop and step through each asset, find the most #recent occurance of the asset in the polls list $pollinfo = SELECT * FROM polls INNER JOIN aps ON polls.ap_id=aps.ap_id WHERE polls.asset='$assetlists[0]' ORDER BY oid DESC LIMIT 1 Can anyone help me combine these into a single query? I have been trying many different LEFT OUTER JOINs, but I can not get the desired results. Below is a current version of the query I am trying to build SELECT * FROM assets LEFT OUTER JOIN (SELECT * FROM polls INNER JOIN aps ON polls.ap_id=aps.ap_id WHERE polls.asset=assets.asset ORDER BY oid DESC LIMIT 1) AS pollstemp ON assets.asset=pollstemp.asset My problem is figuring out how to get the most recent poll record containing the current asset to join with the current record from asset. Thanks in advance! -Ed ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] User defined types -- Social Security number...
Anyone have a good pre-built user-defined type definition for creating / maintaining / manipulating a SSN ... where valid chars are in the range 000-00- through 999-99-. I imagine that the storage column is probably varchar(11) -- I am looking for a type definition that 1) checks that all numbers are in the range [0..9] 2) inserts the dashes at position 3) accepts either '000123456' OR '000-12-3456' as the input 4) leading zeroes (as in the above) must be stored -- hence varchar(11) instead of numeric 5) always outputs in thje format '000-12-3456' Either that or the question is: How can I coerce postgreSQL into using an input / output "mask"... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 ---(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] Function
We installed postgres, but it did not install the default DB "postgres". This is on 7.3.4 postgres. Any thing that we might have missed out? TIA, Sumita -Original Message- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 4:17 AM To: Sumita Biswas (sbiswas) Cc: 'Stephan Szabo'; [EMAIL PROTECTED] Subject: Re: [SQL] Function On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? I was > able to create a function called Proc_ConferenceSummary(). In SQL > Server I used to run it through query analyzer by writing the > following command: exec Proc_ConferenceSummary > '12/1/2003','1/23/2004',1,1,0,5001 > > But I don't know how to do it in Postgresql. select functioname(arg1,arg2,arg3); ---(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] Function
I have a postgres function that returns a boolean, I want to use this
return in another function.
How do I do it?
Will the following code help me fetch it in a variable:
select lv_flag = drop_table_if_exists(''alarmconfig'',false);
drop_table_if_exists is the function which I want to call.
Any help is appreciated.
Thanks,
Sumita
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 18, 2004 4:17 AM
To: Sumita Biswas (sbiswas)
Cc: 'Stephan Szabo'; [EMAIL PROTECTED]
Subject: Re: [SQL] Function
On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote:
> Thanks for the answer.
> I have one more issue. How do I test a function that I wrote? I was
> able to create a function called Proc_ConferenceSummary(). In SQL
> Server I used to run it through query analyzer by writing the
> following command: exec Proc_ConferenceSummary
> '12/1/2003','1/23/2004',1,1,0,5001
>
> But I don't know how to do it in Postgresql.
select functioname(arg1,arg2,arg3);
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Binary retrieval - *Best practice* recommendations...
Hi, I have a table in postgreSQL, ver. 7.4.1, which serves up image data(JPEG) to users browsers. In various posts I have read there has been a favour towards storing image data as bytea (byte[]) instead of storing the filesystem location of the image, or whatever binary data is, in the database. I have decided to store my images on the database and wanted to know what *best practice* performance tweaks you suggest I make to postgreSQL in terms of minimising the time it takes to serve binary data to users? many thanks in advance ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Function
Is this the right place to get PostgreSQL 7.3.4 from: ftp://ftp10.us.postgresql.org/pub/postgresql/source/v7.3.4/postgresql-7. 3.4.tar.gz Regards, Sumita -Original Message- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 4:17 AM To: Sumita Biswas (sbiswas) Cc: 'Stephan Szabo'; [EMAIL PROTECTED] Subject: Re: [SQL] Function On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? I was > able to create a function called Proc_ConferenceSummary(). In SQL > Server I used to run it through query analyzer by writing the > following command: exec Proc_ConferenceSummary > '12/1/2003','1/23/2004',1,1,0,5001 > > But I don't know how to do it in Postgresql. select functioname(arg1,arg2,arg3); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] transaction block causing trouble
Hey I hope someone can help me here. I'm doing some little test on PostgreSQL 7.3.5 and am experiencing some weird behaviour. I create some tables and add values to it using insert into select from. After I am done with that, I create unique constraints on the tables. The reason to do that afterwards was to speed up the process a bit, but that doesn't seem to help anything. Anywayz, all goes well if I don't mention anything about transactions in my file and feed it to pgsql, which will autocommit after each line, if I'm not mistaken. However if I put begin; and commit; before and after the complete bunch of lines, pgsql suddenly encounters an error: psql:test.sql:1346: NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'rkb_unique_head' for table 'rkb' psql:test.sql:1346: ERROR: Cannot create unique index. Table contains non-unique values psql:test.sql:1350: ERROR: current transaction is aborted, queries ignored until end of transaction block psql:test.sql:1356: ERROR: current transaction is aborted, queries ignored until end of transaction block COMMIT The test.sql file contains: begin; drop table rka; drop table rkb; drop table tapestry; commit; begin; create table RKA( head int, tail int); create table RKB( head int, tail int); create table tapestry( attr0 int, attr1 int); ... some 1024 inserts like insert into RKA values (0, 726); insert into RKB select head+0, tail+0 from RKA; insert into RKB select head+8192, tail+8192 from RKA; ... this continued till there are 102400 rows in RKB update RKB set tail=(tail*37) % 102400; update RKB set tail=(tail*31) % 102400; update RKB set tail=(tail*29) % 102400; update RKB set tail=(tail*23) % 102400; update RKB set tail=(tail*19) % 102400; update RKB set tail=(tail*17) % 102400; update RKB set tail=(tail*13) % 102400; update RKB set tail=(tail*11) % 102400; update RKB set tail=(tail*7) % 102400; -- build unique constraints alter table RKA add constraint rka_unique_head unique (head); alter table RKB add constraint rkb_unique_head unique (head); insert into tapestry select R0.head, R0.tail from RKB R0; -- the insert will only take place if the above unique constraint was -- satisfied -- build unique constraint alter table tapestry add constraint tapestry_unique_attr0 unique (attr0); commit; Why does the whole thing run luckily without transactions, and complains about a duplicate entry when in an transaction block? Also when using less insert statements for RKB the transaction does not succeed. When specifying unique when creating the tables, and skipping the add constraint part at the bottom, the whole transaction does succeed. my file can be found gzipped at http://www.cwi.nl/~fabian/test.sql.gz Any help is appreciated for I'm clueless about this issue :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] searching polygons
What query would i have to use to search for an item using a polygon as a parameter? (i.e a very large polygon that would identify smaller polygons within it) ideally i would like to give postgresq a series of co-ordinates and then have it return all those results whose polygons fall into that set of co-ordinates, is this possible? at the moment all i can think of is select * from species where location between '(0,0)' and '(1000,0)' and '(0, 1000)' and '(1000; 1000)'; I think im way off, any suggestions? Cheers Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date Foo.
I'm still not sure what you were looking for.
If you have, say, March 16th, and the next date is August 23rd, do you
want to count March, April, May, Jun, July, August = 6? Or do you want to
count the number of 30 day periods?
Using date_part gets you the first one.
Plus, since months can be 28, 29, 30, or 31 days long, how exactly does
one convert 58 days to months? Without knowing the month the start and
end dates have, you wouldn't know for sure if it was two or three. and
what about parts of months, a week in the end of march, all of april, and
the first two weeks of May, is that two months or three?
I guess my point is that I'm not sure what question you were asking, so
I'm not sure how Perl's Date::Calc did better or worse than would
postgresql.
On Fri, 20 Feb 2004, Brian Knox wrote:
> Sorry, Not looking for a way to extract a month from a timestamp. I'm
> looking for a way to convert an interval from days to months. I'm not
> sure after digging into it that there is a way to handle it in SQL, as
> the interval that results from subtracting one timestamp from another is
> not away of what months the interval spans, so there'd be no proper way
> to take month lengths into account.
>
> I gave up on the sql and used Date::Calc from Perl. Thanks for your
> answer anyway.
>
> scott.marlowe wrote:
> > On Thu, 19 Feb 2004, Brian Knox wrote:
> >
> >
> >>( sorry if this is a repeat, my mail server is being wonky today )
> >>
> >>I'm looking for a way, within SQL, given a starting date and an ending
> >>date, to get back the number of months between the start and end date.
> >>If I "SELECT end_date - start_date", I get back an interval in days; I
> >>need months.
> >
> >
> > Maybe date_part?
> >
> > select date_part('month','2004-08-02 12:00:00'::timestamp) -
> > date_part('month','2004-05-01 12:00:00'::timestamp);
> >
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Date Foo.
Sorry, Not looking for a way to extract a month from a timestamp. I'm
looking for a way to convert an interval from days to months. I'm not
sure after digging into it that there is a way to handle it in SQL, as
the interval that results from subtracting one timestamp from another is
not away of what months the interval spans, so there'd be no proper way
to take month lengths into account.
I gave up on the sql and used Date::Calc from Perl. Thanks for your
answer anyway.
scott.marlowe wrote:
On Thu, 19 Feb 2004, Brian Knox wrote:
( sorry if this is a repeat, my mail server is being wonky today )
I'm looking for a way, within SQL, given a starting date and an ending
date, to get back the number of months between the start and end date.
If I "SELECT end_date - start_date", I get back an interval in days; I
need months.
Maybe date_part?
select date_part('month','2004-08-02 12:00:00'::timestamp) -
date_part('month','2004-05-01 12:00:00'::timestamp);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Row counts/data changes. Any catalog table that has this info?
David, > table_name#Rows > cust 1000 > order 5000 > order_detail 9500 If you're willing to live with some inaccuracy, do: SELECT relname, reltuples FROM pg_class WHERE relkind='r'; This count gets updated when you do a VACUUM, and is seldom 100% accurate; however, if you VACUUM regularly it will be within 5-10%. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating constraint sometime fail in a transaction
Olivier Hubaut <[EMAIL PROTECTED]> writes: > So, we're wondering if the action for putting/removing constraint could > be executed out of the transaction, so that it may overlaps and crash in > such case. > Does anyone notice the same problem? When you didn't show us exactly what you're doing or exactly what went wrong, it's impossible to say ... regards, tom lane ---(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] date format in 7.4
On Friday 20 February 2004 14:58, Silke Trissl wrote: > Hi, > > I have an application where users can enter the date via a web interface. > > Recently I upgrated my PostgreSQL version from 7.3 to 7.4.1. > > On 7.3 I run several tests about the format of the date and found, > that Postgres accepts almost everything. Today I found out, that 7.4.1 > only accepts dates in the format mm-dd-yy, although the documentation > still states the following > 5. >Otherwise the date field ordering is assumed to follow the > DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a > month or day field is found to be out of range. I think what happened is that it will now *only* accept the format specified by your datestyle setting. Previously, as you say, it would have a guess at almost anything (and sometimes get it wrong). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] date format in 7.4
Hi, I have an application where users can enter the date via a web interface. Recently I upgrated my PostgreSQL version from 7.3 to 7.4.1. On 7.3 I run several tests about the format of the date and found, that Postgres accepts almost everything. Today I found out, that 7.4.1 only accepts dates in the format mm-dd-yy, although the documentation still states the following # 5. Otherwise the date field ordering is assumed to follow the DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a month or day field is found to be out of range. # ref: http://www.postgresql.org/docs/7.4/interactive/datetime-appendix.html Does anyone know, if it is intentional to restrict it to mm-dd-yy format or is is just a bug of 7.4.1? Thanks in advance for any suggestions Silke ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] create function atof?
mark <[EMAIL PROTECTED]> writes:
> Is it possible to create a database function that mimics the C function atof?
Just cast.
There doesn't seem to be a pg_cast entry for varchar to float8, but you
could cast to text and then float8, or you could use functional notation
for the cast (which is a tad more forgiving than CAST or ::).
regression=# select '1234.5'::varchar::float8;
ERROR: cannot cast type character varying to double precision
regression=# select '1234.5'::text::float8;
float8
1234.5
(1 row)
regression=# select float8('1234.5'::varchar);
float8
1234.5
(1 row)
Or write a plpgsql function that simply tries to return its input.
I believe that whenever plpgsql is called on to make a type conversion,
it will invoke the output function of the given type and then the input
function of the other type, so it will work for any cases where the
external textual representation looks the same.
regression=# create function atof(varchar) returns float as
regression-# 'begin
regression'# return $1;
regression'# end' language plpgsql strict immutable;
CREATE FUNCTION
regression=# select atof('1234.5');
atof
1234.5
(1 row)
regression=# select atof('zit');
ERROR: invalid input syntax for type double precision: "zit"
CONTEXT: PL/pgSQL function "atof" while casting return value to function's return type
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] create function atof?
O kyrios mark egrapse stis Feb 20, 2004 : > Hello, > > Is it possible to create a database function that mimics the C function atof? > I'm guessing it should look something like this: > > create function atof(varchar) returns float > as '??' > language > returns null on null input; Simply use # select ''::text::float4; or create function atof(varchar) returns float language sql as 'select $1::text::float' returns null on null input; > > Thanks, > > Mark > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] create function atof?
Hello, Is it possible to create a database function that mimics the C function atof? I'm guessing it should look something like this: create function atof(varchar) returns float as '??' language returns null on null input; Thanks, Mark ---(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?
On Wednesday 18 February 2004 09:18, you wrote: > Maybe if bandwidth is a restriction the base64 solution > saves some bandwith, since base64 file is ~ 1.3 times larger > than the original, whereas the escaped octal representation > will be ~ 4 times larger. If you use libpq's v3 protocol with binary format (PostgreSQL 7.4+), the overhead is minimal both ways. -- Best regards, Igor Shevchenko ---(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] pg_restore - don?t restore. Why?
On Fri, Feb 20, 2004 at 12:24:46AM -0300, 2000info wrote: > pg_dump, ok. > pg_restore, don?t restore. Why? If you didn't use a non-ASCII format from pg_dump, you don't need pg_restore. Just use psql. A -- Andrew Sullivan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problem with FOR SELECT in plpgsql function
On Friday 20 February 2004 10:07, Plant Thomas wrote: > I have a problem with the following function: [snip] > > This function sometimes returns null even if there is a record in the > database for the corrisponding id. > This happens only when there is only one record for the corrisponding id > value in the database, if there > are more than one record it works with no problem. If it actually returns NULL and not the empty string, then the loop must be processed, since you set "uhrzeiten" to the empty string before the loop. Two things to check: 1. None of your variables match a column in your select (and you seem OK here). 2. None of the fields in your expression are null (e.g. dat.vormittag_a) I think it's #2 - concat NULL to any string and you get NULL back. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Problem with FOR SELECT in plpgsql function
I have a problem with the following function:
CREATE OR REPLACE FUNCTION "weiterbildung"."f_termine_uhrzeit" (integer)
RETURNS text AS'
DECLARE
id ALIAS for $1;
dat RECORD;
uhrzeiten text;
BEGIN
uhrzeiten = ;
FOR dat IN SELECT vormittag_a, vormittag_e, nachmittag_a, nachmittag_e
FROM weiterbildung.t_termine where id_kurs = id order by datum
LOOP
uhrzeiten :=
uhrzeiten||to_char(dat.vormittag_a,''HH24:MI'')||''(''||to_char(dat.vormitta
g_a,''HH24:MI'')||''-''||to_char(dat.vormittag_e,''HH24:MI'')||''
''||to_char(dat.nachmittag_a,''HH24:MI'')||''-''||to_char(dat.nachmittag_e,'
'HH24:MI'')||'')'';
END LOOP;
RETURN uhrzeiten;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
This function sometimes returns null even if there is a record in the
database for the corrisponding id.
This happens only when there is only one record for the corrisponding id
value in the database, if there
are more than one record it works with no problem.
Is there a bug in the FOR SELECT statement?
Thanks for advice
---(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
