[SQL] when inserting to table, text type parameter become NULL (after big assignment to this parameter)
Hi,
I am trying to get server parameters (pg_settings) using a cursor and
insert them to one column table.
A stored procedure parameter holds the long string.
The insert of this parameter succeeds.
The problem is that the string (the parameter value) is not inserted to
the table.
When selecting, this column shows NULL value.
The procedure :
CREATE OR REPLACE FUNCTION dbu_show_server(monitor_table_id text)
RETURNS int AS
$BODY$
DECLARE
cur1 cursor for
select name,setting,source
from pg_settings;
p_name text default '';
p_value text default '';
p_source text default '';
p_out text default '';
BEGIN
open cur1;
loop
fetch cur1 into p_name,p_value,p_source;
exit when not found;
p_out := p_out || p_name || '=' || p_value || ' source=' ||
p_source || ',';
end loop;
close cur1;
raise notice 'p_out is %' ,p_out;
insert into dbu_monitor_table(id,printable_output)
values(monitor_table_id,p_out);
return 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
To activate the procedure:
select dbu_show_server('Sunday')
The monitor table creation:
create table dbu_monitor_table(id text, printable_output text)
I noticed that it is a matter of the parameter length.
When I defined p_out to be shorter, there was no problem.
For example instead of:
p_out := p_out || p_name || '=' || p_value || ' source=' || p_source ||
',';
I wrote (here it gets only p_name variable):
p_out := p_out || p_name || '=' || p_value ;
In this case column printable_output was filled as expected...
Please help.
Thanks
Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
[EMAIL PROTECTED]
Re: [SQL] Having difficulty writing a "best-fit" query..
In article <[EMAIL PROTECTED]>, "Jamie Tufnell" <[EMAIL PROTECTED]> writes: > Hi list, > I have a many-to-many relationship between movies and genres and, in the link > table I have a third field called which orders the "appropriateness" of the > relations within each movie. > For example: > movie_id, genre_id, relevance (i've used movie/genre titles for clarity here, > but in reality they're id's) > > beverly hills cop, action, 2 > beverly hills cop, comedy, 1 > the heartbreak kid, comedy, 2 > the heartbreak kid, romance, 1 > The above would mean, to my application: > "Beverly Hills Cop is both an Action and a Comedy movie but primarily an > Action > movie." > "The Heartbreak Kid is both a Comedy and a Romance movie but primarily a > Comedy > movie." > First of all, if there's a better way to model this kind of ranking/ordering > of > many-to-many relationships, please let me know. This looks fine to me. > Now, to my problem.. > I'm taking a subset of all my genres, and I want to get ONE row for each movie > in the subset alongside its most appropriate genre (whichever has the highest > relevance). In other words, the best fit. You could use something like that: SELECT m.name, g.name, mg.relevance FROM movies m JOIN mg ON mg.movie_id = m.id JOIN genres g ON g.id = mg.genre_id LEFT JOIN mg mg1 ON mg1.movie_id = mg.movie_id AND mg1.relevance > mg.relevance WHERE mg1.movie_id IS NULL This means that there must not be an link table entry for the same movie with a higher relevance. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] two queryes in a single tablescan
Hi everybody, suppose you have a large table tab and two (or more) queryes like this: SELECT count(*),A FROM tab WHERE C GROUP BY A; SELECT count(*),B FROM tab WHERE C GROUP BY B; is there any way to get both results in a single query, eventually through stored procedure? The retrieved [count(*),A] ; [count(*),B)] data couldnt fit on a single table, of course. The main goal would be to get multiple results while scanning the table[s] once only thus getting results in a faster way. This seems to me quite a common situation but i have no clue whether a neat solution can be implemented through stored procedure. Any hint? Thank you Stefano ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [PERFORM] two queryes in a single tablescan
On 10/17/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> Stefano Dal Pra wrote:
> > suppose you have a large table tab and two (or more) queryes like this:
> >
> > SELECT count(*),A FROM tab WHERE C GROUP BY A;
> > SELECT count(*),B FROM tab WHERE C GROUP BY B;
> >
> > is there any way to get both results in a single query,
> > eventually through stored procedure?
> > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit
> > on a single table, of course.
> >
> > The main goal would be to get multiple results while scanning the
> > table[s] once only
> > thus getting results in a faster way.
> >
> > This seems to me quite a common situation but i have no clue whether a neat
> > solution can be implemented through stored procedure.
>
> With a temp table:
>
> CREATE TEMPORARY TABLE tmp AS SELECT COUNT(*) as rows, a,b FROM WHERE C
> GROUP BY a,b;
> SELECT SUM(rows), a FROM tmp GROUP BY a;
> SELECT SUM(rows), b FROM tmp GROUP BY b;
> DROP TABLE tmp;
>
Thank You.
I actually already do something like that:
in a stored procedure i do create a md5 hash using passed parameters
converted to TEXT
and get a unix_like timestamp using now()::abstime::integer.
This gets me a string like: 9ffeb60e9e6581726f7f5027b42c7942_1192443215
which i do use to
EXECUTE
CREATE TABLE 9ffeb60e9e6581726f7f5027b42c7942_1192443215 AS
SELECT * FROM
getjd('''||param1||''','''||param2||''','||param3||','||param4||')'
The 9ffeb60e9e6581726f7f5027b42c7942_1192443215 is what i called 'tab'
in my first post,
and i need to perform about 7 queryes on that. (after a while i will
drop the table using the timestamp part of the name, but that's
another point).
Here is where i would like to scan once only that table. Depending on
parameters it may get as big as 50Mb (this actually is the tablespace
size growth) or more with about 10^6 tuples.
Stefano
> (Using temp tables in plpgsql procedures doesn't quite work until 8.3.
> But you can use dynamic EXECUTE as a work-around. There used to be a FAQ
> entry about that, but apparently it's been removed because the problem
> has been fixed in the upcoming release.)
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL]
Hello. I am trying to upgrade from PostgreSQL 8.0.3 to PostgreSQL
8.1.10. I have a very simple routine that works fine with the 8.0.3
version:
int easy_connect()
{
exec sql connect to my_db as my_cnxtn;
printf ("connection results:\n");
printf ("code: %d\n", sqlca.sqlcode);
printf ("state: %s\n", sqlca.sqlstate);
printf ("warn: %s\n", sqlca.sqlwarn);
exec sql begin work;
exec sql commit work;
return 0;
}
When I use the 8.1.10 version, the connection works ( sqlca.sqlcode is
0, sqlca.sqlstate is and sqlca.sqlwarn is blank). But it then
crashes on the "exec sql begin work" line.
I get the following error in a window:
Runtime Error!
Program: C:\testSQL.exe
This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's support team for more information.
I removed the 8.0.3 version using "remove program" from the control
panel. I then went and removed my C:\Program Files\PostgreSQL\8.0
folder, just to make sure I was not including the wrong header files,
linking to the wrong libraries, or calling the wrong version of
ecpg.exe.
Thanks for any help.
-Cheryl
