Re: [GENERAL] table name in pl/pgsql

2004-11-25 Thread CoL 
hi,
ON.KG wrote:
New question:
i have tables like
table_20041124,
table_20041125,
etc...
i'm trying to make function (for example):
=
CREATE FUNCTION get_count(text, text)
RETURNS int2 AS '
  DECLARE
cnt int4;
  BEGIN
SELECT INTO cnt COUNT(*)
FROM table_$1   -- That doesn't work
WHERE key = $2;
RETURN cnt;
  END;'
LANGUAGE 'plpgsql';
=
call this function by:
=
SELECT get_count("20041124", "something");
=
string in funstion -  FROM table_$1
how could i get a final correct table name here?
You can use execute for dynamic sql.
CREATE FUNCTION get_count(text, text) RETURNS int2 AS '
declare rec record;
begin
 for rec in execute ''select COUNT(*) as num from table_''||$1||'' 
where key=||$2 '';
 loop
  return rec.num;
 end loop;
return;
end;

PS: anyway, you want returns int2 , but you declared int4 :)
C.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] table name in pl/pgsql

2004-11-25 Thread Tino Wildenhain 
Hi,

Am Donnerstag, den 25.11.2004, 19:42 +0300 schrieb ON.KG:
> New question:
> 
> i have tables like
> table_20041124,
> table_20041125,
> etc...
> 
> i'm trying to make function (for example):
> =
> CREATE FUNCTION get_count(text, text)
> RETURNS int2 AS '
>   DECLARE
> cnt int4;
>   BEGIN
> SELECT INTO cnt COUNT(*)
> FROM table_$1   -- That doesn't work
> WHERE key = $2;
> 
> RETURN cnt;
>   END;'
> LANGUAGE 'plpgsql';
> =
> 
> call this function by:
> 
> =
> SELECT get_count("20041124", "something");
> =
> 
> string in funstion -  FROM table_$1
> 
> how could i get a final correct table name here?

You need to build your query in your function
as a big string and pass it to EXECUTE

(See
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
and
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
)

however encoding data into table names does not sound so elegant
for me - why not make it a real column?
The advantage would be you'd have a real query and let postgres
compile a plan for it - which is not possible for execute.

If you are concerned on index size you can always use partial
indices based on your "table number".

Regards
Tino


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] table name in pl/pgsql

2004-11-25 Thread Richard Huxton 
Adam Witney wrote:
I think you would have to do it something like this, although whether the
SELECT INTO works in an EXECUTE context I am not sure (note, completely
untested code!)
CREATE FUNCTION get_count(text, text) RETURNS int2 AS '
DECLARE
  cnt int4;
BEGIN
  
  EXECUTE ''SELECT INTO cnt COUNT(*) FROM table_'' || $1 || '' WHERE key =
'' || $2;
That won't work either, you'll need to user FOR..IN..EXECUTE:
CREATE TABLE exectest (a integer, b text, PRIMARY KEY (a));
COPY exectest FROM stdin;
1   aaa
2   bbb
3   ccc
\.
CREATE FUNCTION demo_exec_fn() RETURNS boolean AS '
DECLARE
r RECORD;
BEGIN
FOR r IN EXECUTE ''SELECT * FROM exectest''
LOOP
RAISE NOTICE ''a=%, b=%'', r.a, r.b;
END LOOP;
RETURN true;
END
' LANGUAGE plpgsql;
SELECT demo_exec_fn();
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] table name in pl/pgsql

2004-11-25 Thread Adam Witney 

I think you would have to do it something like this, although whether the
SELECT INTO works in an EXECUTE context I am not sure (note, completely
untested code!)

CREATE FUNCTION get_count(text, text) RETURNS int2 AS '
DECLARE
  cnt int4;
BEGIN
  
  EXECUTE ''SELECT INTO cnt COUNT(*) FROM table_'' || $1 || '' WHERE key =
'' || $2;

  RETURN cnt;
END;'
LANGUAGE 'plpgsql';





> New question:
> 
> i have tables like
> table_20041124,
> table_20041125,
> etc...
> 
> i'm trying to make function (for example):
> =
> CREATE FUNCTION get_count(text, text)
> RETURNS int2 AS '
> DECLARE
>   cnt int4;
> BEGIN
>   SELECT INTO cnt COUNT(*)
>   FROM table_$1   -- That doesn't work
>   WHERE key = $2;
> 
>   RETURN cnt;
> END;'
> LANGUAGE 'plpgsql';
> =
> 
> call this function by:
> 
> =
> SELECT get_count("20041124", "something");
> =
> 
> string in funstion -  FROM table_$1
> 
> how could i get a final correct table name here?
> 
> Thanx!
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>  http://archives.postgresql.org


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] table name in pl/pgsql

2004-11-25 Thread ON.KG 
New question:

i have tables like
table_20041124,
table_20041125,
etc...

i'm trying to make function (for example):
=
CREATE FUNCTION get_count(text, text)
RETURNS int2 AS '
  DECLARE
cnt int4;
  BEGIN
SELECT INTO cnt COUNT(*)
FROM table_$1   -- That doesn't work
WHERE key = $2;

RETURN cnt;
  END;'
LANGUAGE 'plpgsql';
=

call this function by:

=
SELECT get_count("20041124", "something");
=

string in funstion -  FROM table_$1

how could i get a final correct table name here?

Thanx!


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org