1. not exactly what you were looking for, but i answer this partially by
putting a commented-out CVS expansion tag (e.g. $Id:) in the body of the
function so that it gets into the catalog and can be searched:
CREATE OR REPLACE FUNCTION foo ()
RETURNS void AS
$BODY$
-- $Id: foo.sql,v 1.6
your problem is a little unorthodox, but i will spare you the "why the
heck do you want to do this?" discussion and assume you have good
reasons... so here's a "dynamic SQL" approach:
select 'create table test (id bigint, '|| array_to_string(array(select
a||' text' from foo),', ')||');';
not pret
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 10, 2007 6:36 AM
>
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > so is it safe to say that an index that has
> > pg_stat_user_indexes.idx_scan,
&g
ks!
george
> -Original Message-
> From: Brad Nicholson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 01, 2006 9:12 AM
> To: George Pavlov
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] finding unused indexes?
>
> On Tue, 2006-08-01 at 09:05 -0700, Geor
before i start reinventing the wheel does anyone have handy a function (or
straight SQL) for calculating the centerpoint coordinates and the radius of the
minimum bounding circle (not the the circumcircle) of a triangle (coordinates
of 3 points given as inputs).
a bonus would be a generalizati
> > As an immediate solution can anyone share a comprehensive query to
> > INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any
> > cross-schema dependencies and halt before issuing a DROP
> with a CASCADE?
>
> Issue the DROP without CASCADE and read the error message.
Well, to be pre
I would like to be able to drop a schema with all of its objects, but
don't want to accidentally drop objects that are in other schemas. If
there are such objects I'd prefer the drop to fail. If I use DROP SCHEMA
FOO CASCADE I run the risk of dropping objects from other schemas that
depend on objec
> How will I enable command string to see the commands?
in your postgresql.conf set stats_command_string = true
read
http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html for
details
---(end of broadcast)---
TIP 3: Have you checked
>Is there a way to see from the log files on what sql statement is
> currently by which user? In other words, I want to monitor
> the DB activity.
for a current snapshot you don't need the logs, try:
select * from pg_stat_activity;
(command string needs to be enabled for your database.)
Thanks all for the various useful thoughts. Let me backtrack a bit and
state my real underlying issue a bit with actual examples. Hope not to
bore you with the length of this. Looks to me like an optimizer issue
unless I am missing something.
So, suppose I have a query:
select *
from stuff
inner
> > BYs on user_id and various subqueries, but my basic thought is that
> > should not really matter...
>
> You're unlikely to get any useful comment on this when you have not
> shown any of those details, nor even an EXPLAIN.
yes, i know. i guess i was partially just venting. sorry. the problem
all my SQL-writin' life i have been assuming that expressions like =,
IN, BETWEEN in the WHERE clause are, in the most general sense,
alternative ways of doing the same things. i am hitting some very very
bizarre results in PGSQL:
i have a (very involved) view, say v_foo, largely optimized to be
q
Yes, the thread did not seem to go very far. The SQL standard does seem
inconsistent in this area, but that is not an argument for allowing data
constraint violation. Until the standard is modified I think it would be
good for the reputation of the DBMS we all love to come up with a fix...
Even t
In 8.1 I have a situation where nullability of user defined datatypes
does not seem to be enforced. Using the following steps I end up with a
table that has a column that should not be nullable, but has nulls in
it. Here's a pared down outline of the steps:
-- create a datatype that should enforc
Why don't you start by presenting the query in a more readable form
(hint use SQL-standard JOIN syntax for all of your joins) and maybe
narrowing just to a couple of tables to isolate the problem. As it is,
it is hard to understand. In the process of rewriting you will be forced
to think about each
Should be entirely executable in postgres using psql variables (once you
get around the psql quoting weirdnesses). do this:
gp_test=# \set item '\''Apple'
gp_test=# \set qty 6
gp_test=# \echo :item
'Apple'
gp_test=# \echo :qty
6
and then run his query.
Now, i am not sure what DBMS lets Mr. Celk
t type(s). You may
need to add explicit type casts.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 31, 2006 1:23 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] record datatype comparison
I am trying to do some record comparisons using IS DISTINCT FROM and I
feel like I am missing something. Basically comparisons between manually
constructed records work as expected, but if I have a record returned by
a select on one (or both sides) of the comparison I get errors "ERROR:
operator do
simplest might be psql -l.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jon Horsman
> Sent: Friday, October 27, 2006 8:13 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] How to query information schema from shell script
>
> In my original
> I'm trying to figure out how i can query the postgres information
> schema from a bourne shell script on linux. I need to know if a
> user/table exists. Does someone mind giving me a quick example of how
> this works, is this possible?
% for tn in `psql -Umnp -dmnp_gp -hstgdb0 -tA -c"select ta
And don't forget that \COPY and especially COPY are usually much faster
(and, IMHO, easier to compose/maintain) than gobs of INSERTs.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono
> Sent: Tuesday, October 10, 2006 1:46 PM
> To: Dirk Ja
Anybody have a clever way to quickly find whether there are any unused
indexes in a PG DB? One way I have done is to take queries from the DB
log, prepend an explain to each and grep the results, but I am wondering
if there are either any index usage stats maintained somewhere inside
Postgres or i
what would you expect the following command to insert into column a:
copy foo (a,b) from stdin with csv;
"bar" , 3
\.
i was expecting to see 'bar', but instead i get 'bar ' (the spaces
between the double quote and the comma get inserted.
select length(a), * from foo;
length | a| b
--
I have a table of names with two subsets of entities. I want to find
those names from set 1 that are substrings of names from set 2 from the
same table. Basically the pared down query I want is something like
this:
select t1.myname, t2.myname
from mytable t1
inner join mytable t2
I have city and postal_code tables linked by city_postal_code through a
city_id and postal_code_id. The postal_codes have latitude/longitude,
the cities don't. I want to set the city lat/long to the average of the
associated postal codes (abstract for a minute on whether that actually
makes sense f
> > test=# insert into foo values (4::int,4::int);
> > INSERT 0 1
> > test=# insert into foo values (4::text,4::text);
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.
> > test=# insert into foo values (cast(4 as
or, from the "stupid tricks" category:
SELECT
n.user_id,
max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note)
FROM notes n
GROUP by n.user_id
i am not *really* suggesting this!
---(end of broadcast)---
TIP 5: don't forget
> Sure, but in this example the required type of the value is clear from
> immediate context (ie, the INSERT). This is one of the cases where
> the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4'
> in this example are *not* values of type text; they are
> untyped literals which
> "Better" is in the eye of the beholder.
sorry for the value-laden term. "laxer" is more appropriate, of course!
the funny thing is that had they cast the NULLs to TEXT it would have
failed there too (they do not do implicit TEXT to INT).
> It surprises me not at all that
> Microsoft would be
What does The SQL Standard say about this one?
create table foo (a varchar, b int);
insert into foo (a, b) select null, null from bar;
-- no problem
insert into foo (a, b) select distinct null, null from bar;
-- ERROR: column "b" is of type integer but expression is of type text
-- HINT: You w
Yes, yes, of course... There are nulls in my t1 table. And, of course,
NOT IN can return THREE possible values: not just TRUE or FALSE but also
NULL...
select distinct (moo.goo not in (null)) from moo;
--> null
select count(*) from moo where moo.goo not in ('gai', 'pan', null)
--> 0, no matter wh
The following looks like a bug to me, but please someone review and tell
me what I might be missing. Seems that past a certain result set size a
"[NOT] IN (subquery)" stops behaving as expected and returns 0 matches
even when there should be matches. No errors are returned, just faulty
data. The ex
I would say that pg_catalog is the more complete one whereas the
information_schema the more generic, standards-conformant place. I would
stick with the information_schema unless that becomes inadequate. A case
in point may be sequences. Apart from
information_schema.columns.column_default I haven'
33 matches
Mail list logo
