I have a function that returns a row from a table, e.g.:
create table foo (a integer);
create function f_foo (integer) returns foo language 'sql'
as $foo$
select * from foo where a = $1 limit 1
$foo$
create table bar (b integer);
select * from bar
cross join f_foo(bar.b) as foo;
Unfortunatel
Michael Glaesemann wrote:
>
> On Jan 14, 2006, at 23:54 , Jeff Boes wrote:
>
>> Tony Wasson wrote:
>
>>
>> Sure, but that was not my question. I want to be able to set the
>> variable on the command line, BUT have it default to a value inside
>> the SQL
Tony Wasson wrote:
On 1/11/06, Jeff Boes <[EMAIL PROTECTED]> wrote:
Stumped: is there any way to set up default values for psql variables
within the .SQL file itself? Obviously, I can do something like:
$ psql -f my_script -v MYVAR=${myvar:-mydefault}
but I would prefer t
Stumped: is there any way to set up default values for psql variables
within the .SQL file itself? Obviously, I can do something like:
$ psql -f my_script -v MYVAR=${myvar:-mydefault}
but I would prefer to have the value stored with the .SQL file, e.g. (if
this actually worked):
\set MYVAR COALE
nori wrote:
> Ok I got it. There is no way to alias all columns of some table with
> some "prefix" that will be visible in result except to alias each
> column.
Only other way would be to write a view for each table, then write all
your queries against the views.
---(end o
Henry Ortega wrote:
> I have the ff table:
>
> id |total| effective|end_date
> john 6 01-01-200502-28-2005
> john 8 03-01-200506-30-2005
>
> How can I return:
> id |total| effective
lly a "username" constructor, making "jboes" out of "Jeff
Boes"). The odd thing is that function f() also looks into the table
"foo" to see if the value it's constructing is truly unique; if it is
not, it tacks on a "1", "2", etc. until i
Given a table like this:
create table primary (
a integer primary key,
b boolean
);
And another like this:
create table secondary (
a integer,
some_other_fields
);
I would like a foreign key constraint on the "secondary" table that
looks something like:
foreign key (a, true) reference
being
generated automatically, based on data found in a database.
If I understand you correctly, then this might be useful:
begin;
select now();
\o tmp.tmp
\qecho 'drop type \"foofookitty\";'
\o
\! psql -f tmp.tmp
select now();
commit;
--
Jeff Boes
?column?
-
2004-02-29 00:00:00
(1 row)
Thus, given the original response to your question:
select * from calendar('1 feb 2004', ( '1 feb 2004'::date +
'1 month'::interval - '1 day'::interval )::date);
--
Jeff Boes
I have a table with an INSERT/UPDATE/DELETE statement trigger. The
trigger's action is to insert "jobs" into a queue noting that the table
has changed.
A number of other tables have FK relationships with this table, and they
have their own statement triggers that fire on DELETE.
When I delete
I have a table with an INSERT/UPDATE/DELETE statement trigger. The
trigger's action is to insert "jobs" into a queue noting that the
table has changed.
A number of other tables have FK relationships with this table, and
they have their own statement triggers that fire on DELETE.
When I delete a n
why won't variable interpolation work when the "-c" flag is
used?
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
..
Ugh, never mind. I finally saw the reason in the 'psql' documentation. Missed it
the first time. (And the second, and third, ...)
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nex
Andrew Perrin wrote:
I have a table of people ("reviewers"), a table of review assignments
("assign"), and a table of review acceptances ("accept"). I would like to
be able to write a query to return the latest (e.g., max(assign_date))
assignment for each reviewer, plus the acc_id field from "accep
Hmm, this is puzzling me:
create or replace function fn_foo(text) returns trigger as '
begin
# Do some stuff with $1
end;
' language 'plpgsql';
CREATE FUNCTION
create table bar (aaa text);
CREATE TABLE
create trigger trg_bar
after insert or update on bar
execute procedure fn_foo('string');
ERROR:
This feels like a flaw in the way inherited tables work.
I have a "template" table used to create other tables (but not by
inheritance; instead the "daughter" tables are created via
create table draft_00123 as select * from draft_template where false;
This is done for somewhat historical reasons,
I'm just curious - why is it not possible to use the "=" operator to
compare values with NULL? I suspect that the SQL standard specified it
that way, but I can't see any ambiguity in an expression like "AND
foo.bar = NULL". Is it because NULL does not "equal" any value, and the
expression should b
This seems ... well, counter-intuitive at least:
(using Pg 7.4.1)
# select to_char('4 minutes'::interval -
'5 minutes 30 seconds'::interval, 'mi:ss');
to_char
-
-1:-3
(1 row)
Why is the trailing zero lost? Why are there two minus signs?
I would expect '-1:30'.
Likewise,
# select to_char('
In the same vein ...
We are at present using BLOBs to store images (but not very many) and generated
output (mostly HTML and XML). The data being stored doesn't require BLOB use
because of size; it's mostly the binary nature of the data.
Lack of satisfaction with the BLOB support (generally diffi
Troels Arvin wrote:
See http://www.acm.org/sigmod/record/issues/0403/index.html#standards for
an article which summarizes the news in SQL:2003.
This is a very useful page; thank you for creating it and for noting it
in this thread!
--
(Posted from an account used as a SPAM dump. If you really wan
e" TRUE, and enough
non-"include" rows to make N. I might end up with more than N rows for a
given value of "query" if there were more than N with "include" set.
I headed off in the direction of groups of SELECTs and UNIONs, and quit
when I got to som
l_a=;
vs.
EXPLAIN
select * from foobar_table where
col_a=::;
--
~~~~| Genius may have its limitations, but stupidity is not
Jeff Boes | thus handicapped.
[EMAIL PROTECTED] |--Elbert Hubbard (1856-1915), American author
---(end of broadca
s:
create table priorities as
select 'LO' as priority, 1 as sort_order
union
select 'N', 2
union
select 'HI', 3;
select * from testtable join priorities using (priority)
order by sort_order;
This last approach is what I've used with code-tables in our system
is error:
WARNING: plpgsql: ERROR during compile of fn_notify near line 5
ERROR: syntax error at or near ""
What gives? Must I use a trigger to get around this?
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer
t;a" such that the sum of "b" values
is as close to one-half of the total sum of "b" over all rows. That is,
select 2.0*sum(b) from foo
where a in (...);
would be approximately the same as
select sum(b) from foo;
Likewise,
select 2.0*sum(b) from foo
where a NOT in
Jan Pips wrote:
How to convert the interval type into integer, float or any "countable" data
type at the time of table creation using
select ... into?
I'm guessing you want something like this:
SELECT EXTRACT('EPOCH' FROM INTERVAL
'1 days 4 hours 15 minu
UBSCRIBER_NAME" , "B"."ACCOUNT_NUMBER" FROM "B"
>
>
How about a SELECT DISTINCT ON?
SELECT DISTINCT ON (account_number)
subscriber_name, account_number
FROM
(SELECT 1 AS sort_order, subscriber_name, account_number FROM &quo
Is there a straightforward way to change the display of a time to another
timezone? For example, my server's environment is EST (-0500); I want to
convert timestamps for display in PST (-0800). What I've been doing is to use a
Pl/Pgsql function to subtract the current timezone-offset and add in t
) VALUES (?)|, undef,
q|O'Malley|) or die $DBI::errstr;
$dbh->commit;
By using the '?' placeholder format for statements, you can pass in any
data without having to worry about quoting.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer
way.
>
The easiest way would be to write a function in a language that supports
some kind of date library. I'm most familiar with Perl and its
Date::Manip library, so that's what I would turn to.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engi
(142 rows)
How can I write a query that will return these as
1 | aaa | 1251
2 | aba | 1197
3 | cax | 1042
... | ... | ...
142 | axq | 23
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt
elect a.relname, b.relname, 0, b.relpages
from pg_class a
join pg_class b
on (b.relname like 'pg_toast_' || a.relfilenode || '%')
where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc offset 20) as "Others") as rows
o
ect second_val
union ...) as TEMP_TABLE
using (SECONDARY_KEY_FLD)
likewise, either taking some number of my desired values in "chunks" or
all at once.
What's likely to work better, and why? (I'm going off to write a
benchmark script, but I'd like to hear some theoretic
h (iscachable);
The same approach can be used to provide a regex match with Perl syntax,
but I don't have an example of that coded up and at hand.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer f
Perhaps this was covered in the 7.2.x release notes, but it's mystifying us:
select to_char(time_link,'999.99') from stat_fetch
where time_link is not null
order by 1 desc limit 10;
(time_link is a double-precision column.) Under 7.1.3, this returns results like:
to_char
-
9.99
In article <PXEu7.602317$[EMAIL PROTECTED]>, "Bob
Swerdlow" <[EMAIL PROTECTED]> wrote:
> How do I get the rows sorted in a case insensitive way?
> SELECT * FROM MyTable ORDER BY Name;
Try
SELECT * FROM MyTable ORDER BY upper(Name);
(or &#
it is natively built in or some 3rd party
> tool that I can use.
Depends on what platform you want to run the report generator on. If
you're happy with a Windows platform, you could connect Access or Crystal
Reports, or a large number of other query
38 matches
Mail list logo
