[SQL] Change owner of function in 7.2.4?
Folks, I have a 7.2.4 database where I accidentally replaced a bunch of functions while logged in as the superuser. Now the normal applicaiton user can't modify them. While I can fix them by dropping the functions as the superuser and re-creating them as the regular user, I can't see how to do this in a transaction to avoid disrupting the database users. Are there any dangers I don't know about in either of the options below? 1) updating "proowner" in pg_proc 2) making the regular user a superuser temporarily, replacing the functions, and then switching them back. Thoughts? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to make a IN without a table... ?
Hi everybody, i ran today in a problem when doing some (too much for me) advanced sql... What i want to do is something like this: SELECT my_var1, my_var2, my_function(my_var1, my_var2) FROM ( SELECT '1', '2', '3', '4' ) AS my_var1_values, ( SELECT '1', '2', '3', '4' ) AS my_var2_values In short, i want to calculate the result of the function my_function for some values of my_var1, cross by some values of my_var2. These values are not taken in a table, but put in directly. They are a lot, so i would prefer not to write the whole thing, line after line. (Let's say 10 values for the first, and 40 for the second => 400 lines of code to maintain...) I really don't see how to do this :-/ Any help is heartfully welcome, David -- [EMAIL PROTECTED] 01.46.47.21.33 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Changing owner of function -- best method?
Josh Berkus wrote: Folks, I have several functions in a 7.2.4 database that I accidentally REPLACEd while logged in as the superuser, instead of the db owner. As a result, the db owner can no longer modify those functions -- they belong to the superuser. As this is a production database, I can't drop the functions and re-create them as a different user ... the interruption in service would not be acceptable. I was wondering whether there were any problems with either of the following methods: A) Updating the pg_proc table to change the function owner; B) Making the db_owner a superuser, replacing the functions, and then making the db_owner a non-superuser again. I believe, either of two should work. I have done it many times in the past... Dima ---(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] Elegant SQL solution:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
> BY month;
>...
> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month". I could also create a view
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
> a more elegant way to do this.
The first solution is probably the best one. It does not seem that "unelegant"
to me. Another way would be just to do it in the application itself.
...or you could consider this one I came up with. Use at your own risk ;)
SELECT dos.mym AS "Month", COALESCE(uno.rc,0) AS "Total" FROM
(SELECT date_part('month',rowdate) AS mym, count(*) AS rc
FROM mytable GROUP BY 1) AS uno
RIGHT JOIN
(SELECT oid::integer-15 AS mym
FROM pg_type ORDER BY oid ASC LIMIT 12) AS dos
USING (mym);
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200306072131
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+4phlvJuQZxSWSsgRAqLRAJsGr5YNiGXKoXBOWq6+3OpSZXOG3ACdFr2F
ywb1tBYllZt6CKtKYhoc7G4=
=6yvp
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Elegant SQL solution:
CGG: > I could create a one-column table with values 1 - 12 in it, and select from > that table with a where clause matching "month". This is probably the simplest, most elegant solution. It is also the "most relational". > I could also create a view > "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. This would be both awkward and have appaling performance. > There MUST > be a more elegant way to do this. Another method would be to write a set returning function that generates the months and corresponds them to a cursor of the totals and outputs that. However, I think your first method is likely to be the fastest and easiest to maintain. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Changing owner of function -- best method?
Josh Berkus <[EMAIL PROTECTED]> writes: > A) Updating the pg_proc table to change the function owner; That seems safe enough. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Join" on delimeter aggregate query
Michael A Nachbaur wrote: Source Target [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], It isn't clear from your description what you want (to me, anyway), but I'd guess something like this: regression=# select * from src2tgt; source| target -+-- [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] (4 rows) create or replace function mylist(text,text) returns text as ' declare result text; begin if $1 = then result := $2; else result := $1 || '','' || $2; end if; return result; end; ' language 'plpgsql'; create aggregate tgtlist ( BASETYPE = text, SFUNC = mylist, STYPE = text, INITCOND = '' ); regression=# select source, tgtlist(target) from src2tgt group by source; source| tgtlist -+--- [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] (2 rows) HTH, Joe ---(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] Creating Views with Column Names based on Distinct Row Data
Hi All,
(I'm sure I'm not the first person to want to do this, but I didn't see any
mention of it in the FAQ or developers FAQ nor in the docs - if I've missed
something, a gentle pointer will be fine :)
I was wondering if it is possible to create a table view based on a table
which is effectively an "attribute list".
For example, suppose I have two tables:
CREATE TABLE user
(
userid integer,
username character varying,
userpassword character varying,
startdate date
);
CREATE TABLE userdetail
(
userid integer,
attributename character varying,
attributevalue character varying
);
"user" holds pre-defined details about a user (things which are common to all
users).
"userdetail" holds (name,value) pairs about users.
Now I want to make a public view of the user, which would have all of the
defined fields in user, and all of the defined attributes across userdetail.
(e.g. suppose we have 2 types of user - typeA has attributes x, y, z and typeB
has attributes a, b. Then I'd want my public view to look like):
CREATE TABLE PvUser
(
userid integer,
username character varying,
userpassword character varying,
startdate date,
x character varying,
y character varying,
z character varying,
a character varying,
b character varying
);
It is possible to do this (i.e. have the public view created by specifying the
column names "AS" the distinct value of a column in rows in another table?
CREATE VIEW PvUser AS
SELECT
u.userid
u.username
u.password
u.startdate
-- For each unique attributename in userdetail
ud.attributevalue AS {Value of ud.attributename}
FROM
user u, userdetail ud
;
Is what I'm trying to do feasible?
Thanks for any and all help,
Damien
---(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] (long) What's the problem?
> rposition() is a volatile custom C function. Does the query function as expected when not being EXPLAINed ? Odds are it's a bug in the custom C function. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] "Join" on delimeter aggregate query
Hi
You can create an aggregate function to solve this. A friend of mine asked
the same question a while ago, and I created a possible example solution
for him, which I paste here:
CREATE FUNCTION concat(varchar,varchar) RETURNS varchar
AS 'SELECT CASE
$1 WHEN \'\' THEN $2
ELSE $1 || \',\'|| $2
END AS RESULT;'
LANGUAGE SQL;
/* DROP AGGREGATE concat(varchar); */
CREATE AGGREGATE concat (
BASETYPE = varchar,
SFUNC = concat,
STYPE = varchar,
INITCOND = ''
);
/* Example code */
DROP TABLE test;
CREATE TABLE test (
a varchar,
b varchar
);
INSERT INTO test VALUES ('A', '1');
INSERT INTO test VALUES ('A', '3');
INSERT INTO test VALUES ('A', '2');
INSERT INTO test VALUES ('B', 'a');
INSERT INTO test VALUES ('C', 'b');
INSERT INTO test VALUES ('C', 'c');
SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
/*
a | concat
---+-
A | 1,2,3
B | a
C | b,c
*/
The ORDER BY is included to sort the rows before they are aggregated. I'm
not sure that this guarantees that they actually will be sorted, but maybe
some of the postgresql hackers can confirm/deny this? I guess this isn't
important to you, though.
On Fri, 6 Jun 2003, Michael A Nachbaur wrote:
> Hello everyone,
>
> I've set up PostgreSQL as the authentication / configuration database for my
> mail server (Postfix + Courier-IMAP), and though it works beautifully, I need
> some help on my aliases query.
>
> You see, define aliases in a database table as rows in a column in the form of
> "Source" and "Target". The problem is that one source address can be
> delivered to multiple targets (e.g. internal mailing list, or a temporary
> forward to another address), but postfix only processes the first record
> returned from an SQL query.
>
> Postfix can deliver to multiple targets, if you separate the targets with
> comas, like so:
>
> Source Target
> [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],
>
> What I would like to do, is something like the following (I know I'd need to
> group the query, but you get the idea):
>
> Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases
>
> Is there any way this can be done with Postfix?
>
>
Eivind
--
| Mail: [EMAIL PROTECTED] | Lazy on IRC
| HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret
| Tlf: 22187123/93249534 | inn."
| | -- Yang Tse Lyse
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Using a RETURN NEXT
Hi all, I found this example from Postgres site create or replace function GetRows(text) returns setof record as ' declare r record; begin for r in EXECUTE ''select * from '' || $1 loop return next r; end loop; return; end ' language 'plpgsql'; I am trying to use the same idea but in this way .. CREATE OR REPLACE FUNCTION word_case() RETURNS setof RECORD AS' DECLARE reg record; BEGIN FOR reg IN SELECT message FROM rtable LOOP RETURN next reg; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; But at execution time I am getting thi error : WARNING: Error occurred while executing PL/pgSQL function word_case WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set I also tried using my own type defined but I got the same error. Any hint or idea will be appreciated ... Regards Larry _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(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] Using a RETURN NEXT
Mr Weinbach, Larry wrote: But at execution time I am getting thi error : WARNING: Error occurred while executing PL/pgSQL function word_case WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set You didn't show the execution time SQL statement, but the error you are getting usually indicates you did something like: SELECT word_case(); but you should have done: (define the function to return setof record) SELECT * FROM word_case() AS (message text); or CREATE TYPE word_case_type AS (message text); (define the function to return setof word_case_type) SELECT * FROM word_case(); HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] (long) What's the problem?
David Olbersen wrote:
Anybody have any ideas about a problem with this query?
urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
QUERY PLAN
---
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text, (urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)
OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:
urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
If I would be you I would unlimit the coresize in the environment the
postmaster is running, eventually recompile with debug symbols enabled
and look at the stack backtrace of the coredump created when then
backend crashes.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Domains and Joins
On Thu, 2003-06-05 at 17:38, chester c young wrote: > -- standard setup: > create table t1( c1 int primary key, data text ); > create domain dom_c1 int references t1 on delete cascade; This won't work. Domains support NOT NULL, and CHECK constraints. Foreign keys are not allowed on domains at this time in PostgreSQL or in the SQL99 spec. > create table t2( c2 int primary key, c1 dom_c1, moredata text ); > -- will not work with "using" > create view v1 as select t1.*, t2.moredata > from t1 join t2 using( c1 ); It appears to be a complaint about mis-matched datatypes, which is partially true. It's also a problem in 7.4. I'll see what I can do to fix it. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Elegant SQL solution:
On Friday 06 June 2003 18:26, Chris Gamache wrote: > I could create a one-column table with values 1 - 12 in it, and select from > that table with a where clause matching "month". I could also create a view > "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST > be a more elegant way to do this. You probably need a pivot table (the one-column table with values 1 - 12). Oracle Magazine had a useful article on this subject (relevant for none-Oracle SQL too) a while back: http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html (registration probably required). Of course you could also use a set returning function a la: CREATE OR REPLACE FUNCTION months() RETURNS SETOF INT AS ' BEGIN FOR i IN 1..12 LOOP RETURN NEXT i; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Join" on delimeter aggregate query
On Fri, Jun 06, 2003 at 10:03:29 -0700, Michael A Nachbaur <[EMAIL PROTECTED]> wrote: > > Postfix can deliver to multiple targets, if you separate the targets with > comas, like so: > > Source Target > [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], > > What I would like to do, is something like the following (I know I'd need to > group the query, but you get the idea): > > Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases > > Is there any way this can be done with Postfix? You could write an aggregate function to concatenate email addresses with comma separators. ---(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] How to make a IN without a table... ?
On Wed, Jun 04, 2003 at 16:59:02 +0200, David Pradier <[EMAIL PROTECTED]> wrote: > > In short, i want to calculate the result of the function my_function for > some values of my_var1, cross by some values of my_var2. > These values are not taken in a table, but put in directly. > They are a lot, so i would prefer not to write the whole thing, line > after line. (Let's say 10 values for the first, and 40 for the second => > 400 lines of code to maintain...) > > I really don't see how to do this :-/ You could programatically generate the sql code and use union (e.g. (select 1 union select 2 union select 3 union select 4)) to join the rows together or you could write a set returning function. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] EXTERN JOIN with WHEN query
I didn't read all the entire problem but try to use WHERE instead of WHEN. Regards Gaetano Mendola - Original Message - From: "javier garcia - CEBAS" <[EMAIL PROTECTED]> To: "pgsql-sqL" <[EMAIL PROTECTED]> Sent: Thursday, June 05, 2003 12:55 PM Subject: [SQL] EXTERN JOIN with WHEN query > Hi all; > This is a query that I guess is not very dificult, but I'm a newbie; > I've got a lot of tables, each of them with two columns: > > SELECT * FROM precal; -> > (date) (real) >fecha| precipitacion > +--- > 1996-01-01 | 0.6 > 1996-02-01 | 0.7 > ... > > > But in this table there are some inexistents records (some missing days) > And I would like to create lists with a full list of dates and corresponding > precipitation data, with gaps when the row didn't exist. > So; I've created a table with a complete series of dates from 1950 up to > date, and made the query: > > SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON > (fechas.fecha = precal41.fecha); > > This is perfect. But to make it better, would like to include just the dates > from the first one in the precal table. So, I've tried: > > SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON > (fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha); > With the answer: > > ERROR: parser: parse error at or near "WHEN" > > Could you help me with this query? > > Thanks and regards > > Javier > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
