Re: [SQL] select a list of schema names
On Fri, Mar 25, 2005 at 09:50:18PM +0200, Andrus Moor wrote: > > How to select a list of schema names which current user is authorized to > access ? See "System Catalogs" and "System Information Functions" (or "Miscellaneous Functions") in the documentation. Here are links to documentation for the latest release: http://www.postgresql.org/docs/8.0/interactive/catalogs.html http://www.postgresql.org/docs/8.0/interactive/functions-info.html Something like this might be what you're looking for: SELECT nspname FROM pg_namespace WHERE has_schema_privilege(nspname, 'USAGE') ORDER BY nspname; Note that USAGE privilege on a schema doesn't necessarily mean the user has any privileges on the objects in that schema. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to make update statement to work
"Andrus Moor" <[EMAIL PROTECTED]> writes:
> UPDATE demo.toode
> SET "liik"=NULL,"grupp"=NULL
>WHERE ("grupp","liik") NOT IN
> (SELECT ("grupp", "liik") FROM "artliik")
> ERROR: operator does not exist: character = record
> How to write this UPDATE statement properly ?
Hmm ... Postgres wants it without the innermost parentheses:
(SELECT "grupp", "liik" FROM "artliik")
Offhand though I am not certain whether the way you wrote it is supposed
to be allowed according to the SQL spec. Does anyone think this should
have worked, and if so what's the chapter and verse that says so?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Accessing other Databases
On Mon, Mar 21, 2005 at 11:11:26AM -0300, [EMAIL PROTECTED] wrote: > > Anybody knows some way to access other database inside of a different > database(under the same instalation of postgres)? See the contrib/dblink module. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is there a way to find a schema name
On Thu, Mar 24, 2005 at 08:03:48AM -0800, Kalyani Chennupati wrote: > Would like to know if there is a way to find a schema > name (when in the schema through a application or > through psql). See "System Information Functions" (or "Miscellaneous Functions") in the "Functions and Operators" chapter of the documentation. Here's a link to the documentation for the latest release: http://www.postgresql.org/docs/8.0/interactive/functions-info.html See also the "System Catalogs" chapter -- you can do joins on pg_class.relnamespace = pg_namespace.oid, for example. http://www.postgresql.org/docs/8.0/interactive/catalogs.html If this doesn't answer your question then please be more specific about what you're trying to do. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
Title: Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
Code within a function is like this:
SELECT 1
INTO x
FROM pg_tables
where tablename = 'globals_pac_adressarten'
and tableowner = user;
IF (NOT FOUND) THEN
CREATE TEMPORARY TABLE globals_pac_adressarten
(INT_ALIAS_NAME VARCHAR(12) NOT NULL
,KOMMUNIKATION SMALLINT NOT NULL
,POSTALISCH SMALLINT NOT NULL
)
WITHOUT OIDS
ON COMMIT PRESERVE ROWS;
END IF;
When executing following errors are reported:
ERROR: syntax error at or near "$1" bei Zeichen 87
ANFRAGE: CREATE TEMPORARY TABLE globals_pac_adressarten (INT_ALIAS_NAME VARCHAR(12) NOT NULL , $1 SMALLINT NOT NULL ,
$2 SMALLINT NOT NULL ) WITHOUT OIDS ON COMMIT PRESERVE ROWS
KONTEXT: PL/pgSQL function "padr_insert_address" line 142 at SQL statement
SQL statement "SELECT padr_insert_address('pCursor','1','1','xxx','0')"
PL/pgSQL function "test" line 6 at select into variables
ZEILE 1: ...dressarten (INT_ALIAS_NAME VARCHAR(12) NOT NULL , $1 SMALLI...
Would anybody know where those $1 and $2 come from?
If I run the "CREATE" under psql it's working OK;
Help appreciated……
Franz Stuetzle
Schertlinstr. 11-144
D-86159 Augsburg
[SQL] detaching triggers
Hi folks, is it somehow possible to detach trigger, so the calling transaction can return immediately, even before the trigger function has returned. I've got to do some quite complex things which may need some time, when some clients fill in some data, but the results are quite uninteresting to these clients - its important that the client's query is finished as fast as possible. The only solution I currently know is to fill somethings in a queue table by rule and have an external daemon looking at it every second. But this doesnt seem very optimal for me. Any better idea out these ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgre: 8.0.1 Create Table insde a function gives strange error at execution time
"Franz Stuetzle" <[EMAIL PROTECTED]> writes: > ERROR: syntax error at or near "$1" bei Zeichen 87 > ANFRAGE: CREATE TEMPORARY TABLE globals_pac_adressarten (INT_ALIAS_NAME > VARCHAR(12) NOT NULL , $1 SMALLINT NOT NULL , > $2 SMALLINT NOT NULL ) WITHOUT OIDS ON COMMIT PRESERVE ROWS > Would anybody know where those $1 and $2 come from? Undoubtedly they are from plpgsql variable substitution. As a general rule, variables in a plpgsql function should never be named the same as any table or field name that you need to access in that function, because plpgsql isn't smart enough to tell whether it ought to substitute its variable for a reference or not. It will always do so, even in cases where there arguably might be a way for it to tell that it shouldn't (and there are cases where it simply couldn't tell, anyway). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] (non)zero function
Hi folks, is there an function returning true if the argument is null or zero (just to make some queries more concise). Of course its trivial to implement as SQL function, but if postgresql already provides such a function, I would prefer using it. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Foreign key
On Fri, Mar 25, 2005 at 16:31:16 +0100, [EMAIL PROTECTED] wrote: > > When i add table with foreign key in my database, this error return : < > number of referencing and referenced colums for foreign key disagree>. > > How resolve this problem ? Besides what Mike said, one other thing to remember is that if you don't specify columns in the referenced table, the primary key of that table is used, NOT columns with names matching those of the referencing table. In cases like this it have helped if you had copied and pasted an example displaying the problem in addition to the error message. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] detaching triggers
On Sun, 27 Mar 2005 17:41:02 +0200, Enrico Weigelt <[EMAIL PROTECTED]> wrote: > > Hi folks, > > is it somehow possible to detach trigger, so the calling transaction > can return immediately, even before the trigger function has > returned. No, but see below. > The only solution I currently know is to fill somethings in a queue > table by rule and have an external daemon looking at it every second. > But this doesnt seem very optimal for me. Take a look at the LISTEN/NOTIFY interfaces in the docs. This will allow async post-transaction processing. You can set up an ALSO rule to notify when a particular type of statement has executed against your target table. http://www.postgresql.org/docs/8.0/static/sql-listen.html http://www.postgresql.org/docs/8.0/static/sql-notify.html See the bottom of: http://www.postgresql.org/docs/8.0/static/sql-createrule.html Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] (non)zero function
On Sun, Mar 27, 2005 at 17:58:07 +0200, Enrico Weigelt <[EMAIL PROTECTED]> wrote: > > Hi folks, > > > is there an function returning true if the argument is null or > zero (just to make some queries more concise). Of course its > trivial to implement as SQL function, but if postgresql already > provides such a function, I would prefer using it. No. ---(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] "Flattening" query result into columns
Thanks to all for the useful replies. I chose this approach from Scott
Marlowe, which can meet the requirements I work against. Arrays seemed to
have some issues with element with null elements.
/Thomas.
-Oprindelig meddelelse-
Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
På vegne af Scott Marlowe
Sendt: 22. marts 2005 01:46
Til: Thomas Borg Salling
Cc: pgsql-sql@postgresql.org
Emne: Re: [SQL] "Flattening" query result into columns
On Mon, 2005-03-21 at 15:57, Thomas Borg Salling wrote:
> I am looking for a way to flatten a query result, so that rows are
> transposed into columns, just as asked here for oracle:
>
>
http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:en
-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com
>
>
>
> Is there any way to do this with pgsql ?
Here's one from work that allows you to do the same basic thing without
a separate cross table:
select
a.lt ,
b.perspective as XYZ_pers,
b.averageresponsetime as XYZ_aver,
b.lowestresponsetime as XYZ_lowe,
b.highestresponsetime as XYZ_high,
b.totalcount as XYZ_tota,
c.perspective as ABC_pers,
c.averageresponsetime as ABC_aver,
c.lowestresponsetime as ABC_lowe,
c.highestresponsetime as ABC_high,
c.totalcount as ABC_tota
from (
select distinct date_trunc('minutes', lastflushtime) as lt from
businessrequestsummary
where lastflushtime between '2005-03-14 18:42:34' and '2005-03-21
18:42:34'
and perspective in ('XYZ','ABC')
) as a
left join (
select date_trunc('minutes', lastflushtime) as lt,
max(perspective) as perspective,
floor(avg(averageresponsetime)) as averageresponsetime,
min(lowestresponsetime) as lowestresponsetime,
max(highestresponsetime) as highestresponsetime,
sum(totalcount) as totalcount
from businessrequestsummary
where perspective ='XYZ'
group by date_trunc('minutes', lastflushtime)
) as b
on
(a.lt=b.lt)
left join (
select date_trunc('minutes', lastflushtime) as lt,
max(perspective) as perspective,
floor(avg(averageresponsetime)) as averageresponsetime,
min(lowestresponsetime) as lowestresponsetime,
max(highestresponsetime) as highestresponsetime,
sum(totalcount) as totalcount
from businessrequestsummary
where perspective ='ABC'
group by date_trunc('minutes', lastflushtime)
) as c
on
(a.lt=c.lt)
IT's generated by a script that makes it as big as we need for all the
different perspectives.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 8: explain analyze is your friend
