guage
plpgsql;
When I run queries (and expect the same result)
select d from plain_sql(2);
select d from plpg_sql(2);
I get: "(2,)" and "(2,"()")"
I assume there is no way to get sql like result from plpgsql
function?
Regards,
Rikard
--
Rikard Pav
On Sun, 25 Oct 2015 22:31:03 +0100
Rikard Pavelic wrote:
> I assume there is no way to get sql like result from plpgsql
> function?
I should try harder ;)
Managed to get it working using array and array_agg.
Regards,
Rikard
--
Rikard Pavelic
https://dsl-platform.com/
http://template
On 18.9.2010 11:36, Arjen Nienhuis wrote:
> I'm not sure what you want but maybe it's this:
>
>
> => select * from (select t from t) sq;
>t
> ---
> (1,x)
> (1 row)
>
> => select (sq.t).* from (select t from t) sq;
> a | b
> ---+---
> 1 | x
> (1 row)
>
>
I know how to expand record to
On 18.9.2010 4:31, Tom Lane wrote:
> Rikard Pavelic writes:
>
>> For basic query:
>> select t from t
>> result is of type t.
>>
> yeah ...
>
>
>> If I query:
>> select sq from (select t from t) sq;
>> result is of type record.
&
I'm puzzled if this is by design or just overlooked...
create table t(a int, b varchar);
insert into t values(1,'x');
For basic query:
select t from t
result is of type t.
If I query:
select sq from (select t from t) sq;
result is of type record.
I need to query like this:
select (sq).t from (
Hi!
Does Postgres needs to wait for this lock
or is this something that can be fixed?
Create data:
create table test_table
(
id varchar primary key
);
insert into test_table
select i::text
from generate_series(1000,10) i;
Session 1:
select * from test_table where id like '1050%';
Bill Moran wrote:
> Have you tried altering the table, then disconnect and reconnect, then run
> your stored procedure?
>
> If that works, then the problem has to do with OID caching, which is known.
>
>
Yes, I even tried restarting postgres, but nothing helped.
Regards,
Rikard
---
Hi!
I noticed that some of the functions I created don't work anymore.
So I tired to reproduce the problem and this is what I came up with.
create table test(
id serial primary key,
tekst text);
insert into test values(1,'1'),(2,'2');
create function sel_test() returns test as
$$
declare red re
Alvaro Herrera wrote:
>
> I meant the queries inside the function.
>
>
Oh ;(
Here it is
"HashAggregate (cost=825.10..825.19 rows=1 width=112) (actual
time=59175.752..59176.301 rows=75 loops=1)"
" -> Nested Loop Left Join (cost=443.57..825.06 rows=1 width=112)
(actual time=148.338..58997.5
Alvaro Herrera wrote:
> Try doing a PREPARE and then EXPLAIN EXECUTE, like
>
> alvherre=# prepare foo as select generate_series(1, $1);
> PREPARE
>
> alvherre=# explain analyze execute foo(100);
> QUERY PLAN
>
> --
Is this possible?
I've been searching posts, but to no luck ;(
I have one SQL query inside function, but when
i do select from function it takes 8 sec.
If I execute just SQL query (with some parameters passed to it)
it takes 0.3 seconds.
What I'm trying to do is select part of the data from the
Tom Lane wrote:
> Rikard Pavelic <[EMAIL PROTECTED]> writes:
>
>> I'm looking for recommendation for tracking DDL changes on
>> single database instance.
>>
>
> Perhaps "ALTER DATABASE mydb SET log_statement = ddl" would do what
>
Hi!
I'm looking for recommendation for tracking DDL changes on
single database instance.
Currently I'm using pg_log to extract DDL changes, but those changes
are cluster wide.
Ideally I would like to enable option in pg_log to give me info about
in which database changes were made.
Something lik
Hi!
Can someone help me with this problem.
When I select from this function I get an error
ERROR: record "red" has no field "id"
SQL state: 42703
Context: PL/pgSQL function "select_ex1" line 4 at assignment
Here is the code
create table example1(
id serial primary key,
name1 varchar(10),
valu
Shane Ambler wrote:
You can but the default is to allow the execution of all functions.
You need to revoke the existing permission of executing all functions
before you can allow only a single function to be run.
If you want a specific function to be accessed only by selected roles
then you
Bill Moran wrote:
Hmm, so the answer to my question
"How can I assign execute permission to a role for a single function
inside schema."
is I can't?
How did you interpret "do it like this" to mean "you can't do it"?
REVOKE ALL ON FROM PUBLIC;
So this basically means that I can't fi
Tom Lane wrote:
No, it's operating as designed. Per the GRANT reference page:
: Depending on the type of object, the initial default privileges may
: include granting some privileges to PUBLIC. The default is no public
: access for tables, schemas, and tablespaces; CONNECT privilege and TEMP
: ta
Hi!
How can I assign execute permission to a role for a single function
inside schema.
For example
I create schema example;
function example.simple_select()
and user test_user;
If I grant usage on schema example to user test_user as
GRANT USAGE ON SCHEMA example TO test_user;
I can do select
Richard Huxton wrote:
Rikard Pavelic wrote:
I know setof record will do if I explicitly name OUT parameters.
But I want Postgre to figure out for himself what parameters to
return as out parameters.
I don't see why it would make things very ambiguous.
Think about what happens if yo
Martijn van Oosterhout wrote:
Just "setof record" will do. As for the implicit declaration of
variable names, that's harder. I don't know if you can do that without
making things very ambiguous.
I know setof record will do if I explicitly name OUT parameters.
But I want Postgre to figure ou
Shoaib Mir wrote:
You can use a SETOF function as:
CREATE OR REPLACE FUNCTION get_test_data (numeric)
RETURNS SETOF RECORD AS
$$
DECLARE
temp_recRECORD;
BEGIN
FOR temp_rec IN (SELECT ename FROM emp WHERE sal > $1)
LOOP
RETURN NEXT temp_rec;
END LOOP;
RETURN;
E
Hi!
Is there any plan to add implicit declaration of returning parameters
for functions?
Something like:
create function list(in a int) returns setof implicit record as
$$
if a=1 then select * from table1;
else select * from table2;
end if;
$$
languge sql;
which would than dynamically create o
Tom Lane wrote:
Is there any way to group functions logically?
Put them in different schemas, perhaps?
I thought of that, but that is not what I want.
I want function to be in more that one group, so
this would cause even more mess.
Best regards,
Rikard
Hi!
Is there any way to group functions logically?
It's get pretty frustrating to locate some function when
you have 500+ functions :(
And if there isn't is there any plan to add this functionality?
Thanks,
Rikard
---(end of broadcast)---
Recently I saw that there are many changes regarding stored procedures and
I like all of them, but I have a problem with permission issue.
If I grant execution right on function to some user or group PostgreSQL
is unable
to execute this function unless it has all the necessary rights (select,
in
25 matches
Mail list logo
