Josh Berkus wrote:
Tom et al.
Discovered this quirk in foriegn keys:
In the preliminary version of a database, I added foriegn
key constraints to a number of tables, linking them to a
column in a shared reference table (status.status) that was
only one-half of a composite primary key (and
Tom Lane wrote:
I have looked a little bit at what it'd take to make SELECT INTO inside
an EXECUTE work the same as it does in plain plpgsql --- that is, the
INTO should reference plpgsql variables, not a destination table.
It looks to me like this is possible but would require some
Title: RE: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'
What I wrote wasn't about temp tables, it was about selecting into plpgsql variables. It would appear that Jan's syntax gets around this problem.
MikeA
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: 08
Tom Lane wrote:
Michael Ansley [EMAIL PROTECTED] writes:
CREATE FUNCTION table_count(varchar) RETURNS integer AS '
DECLARE
SQL varchar;
RES integer;
BEGIN
SQL = ''SELECT * INTO temp1 FROM '' || $1;
EXECUTE SQL;
SELECT count(*) INTO RES FROM temp1;
RETURN(RES)
END;
'
Hello,
I have upgraded my DB to 7.0.3, but there is still the problem. I think that
it may be a bug.
Joining 3 tables was not possible...
It says "fa1 should be in aggregate too" !
While joining 2 tables gives wrong results ...
ie, if the results should be like this:
fa1 sum
-
Josh Berkus wrote:
Tom, Jan, Michael,
While I have not looked closely, I seem to recall that plpgsql handles
INTO by stripping that clause out of the statement before it's passed to
the SQL engine. Evidently that's not happening in the EXECUTE case.
Jan, do you agree this is a bug?
Jan Wieck [EMAIL PROTECTED] writes:
I am inclined to keep our options open by forbidding EXECUTE 'SELECT
INTO ...' for now. That's more than a tad annoying, because that leaves
no useful way to do a dynamically-built SELECT, but if we don't forbid
it I think we'll regret it later.
You
Josh Berkus wrote:
If you think that's the best way. What we're really all wanting is a wy
in PL/pgSQL to pass a parameter as an object name. Doing it *without*
using EXECUTE would be even better than modifying EXECUTE to accomdate
SELECT ... INTO variable.
If we can write queries that
After you load the data, you need to run vacuum analzye. That'll
get statistics on the current data in the table. Of course, I'm
not sure that'll help in this case.
On Thu, 8 Feb 2001, Brice Ruth wrote:
Stephan,
Here is what EXPLAIN shows:
NOTICE: QUERY PLAN:
Sort
All right ... after reading up on the documentation for vacuum, I
understand why that's necessary. I've run vacuum analyze on all the
tables, now. Here are the more realistic results from explain:
NOTICE: QUERY PLAN:
Sort (cost=62.46..62.46 rows=14 width=64)
- Nested Loop
On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote:
Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).
-Brice
On many systems (linux at least) there is a command
Brice -
What does EXPLAIN say for that query? With empty tables, I get two index scans,
a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal
sort strategy happening somehow, given the four fold ORDER BY clause.
Ross
Here's the empty version:
NOTICE: QUERY PLAN:
Ross,
Thanx to Stephan's help, I found out that after loading the tables w/
data, I had to run vacuum analyze to inform the optimizer of the amount
of data in the table (amongst other things, I imagine). After running
that on all the tables, the query performs fine.
-Brice
"Ross J. Reedstrom"
Brice Ruth [EMAIL PROTECTED] writes:
As for vacuum analyze - prior to running into these problems, I deleted
all data from the database (using delete from tblname) and then ran
vacuumdb -a, after which I loaded the data into the tables using 'copy
... from' - there have been no updates to the
Tom Lane writes:
I am inclined to keep our options open by forbidding EXECUTE 'SELECT
INTO ...' for now. That's more than a tad annoying, because that leaves
no useful way to do a dynamically-built SELECT, but if we don't forbid
it I think we'll regret it later.
You can always use CREATE
Peter Eisentraut [EMAIL PROTECTED] writes:
Tom Lane writes:
I am inclined to keep our options open by forbidding EXECUTE 'SELECT
INTO ...' for now. That's more than a tad annoying, because that leaves
no useful way to do a dynamically-built SELECT, but if we don't forbid
it I think we'll
Hi,
How might I insert a new row into a table and return the id of the new row
all in the same SQL statement? The id is generated by a sequence. Up to
now I've been getting the nextval of the sequence first and then inserting
with the id in a second SQL exec. Is there a faster way (in a general
You can use two quote characters to get a single quote in the quoted
string, so ''month''
On Thu, 8 Feb 2001, Hubert Palme wrote:
Stephan Szabo wrote:
Functional indexes cannot currently take constant values to the function,
so it's complaining about the constant 'month'. The current
I want to query this...
-- SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
-- CREATE INDEX idx_bbs ON bbs (ref, step);
but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result
"Kim Yunhan" [EMAIL PROTECTED] writes:
I want to query this...
-- SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
-- CREATE INDEX idx_bbs ON bbs (ref, step);
Well, no. The ordering the query is asking for has nothing to
20 matches
Mail list logo
