0
I need to write a temporary table in sql to use in a subquery. I already have two ways, but they produce unpleasant effects.
I imagine there’s another way to create this temporary table, but I haven’t figured out how to do it yet.
Form 1:
select letra from (
(select 'a' as letra)
union
(select 'b' as letra)
) as tabela_temporaria
Pros: uses no Function.
Against: in the example that I leave has only 2 lines, but occasionally they can have innumerable lines and the sql becomes giant, because for each one I need to write (select 'x' as letra) union
Form 2:
select letra from
(select unnest(array['a', 'b']) as letra) as tabela_temporaria
Pros: sql gets reduced.
Contra: uses a Function which makes performance more time consuming.
Goal: I imagine that there is some other way more correct, without use of functions, something like:
Select letra from ('a', 'b', 'c') as letra
Any idea?
Pq does not create a view then?
– Bruno Warmling
To use view I would have before running my sql to create a view, then use it, and then drop view. or do you have some way to use temporary view? only for this sql? because with each sql execution the lines can change, for example. in a moment may be 'a', 'b', 'c', another moment may be other’d', 'and' ... , they are not fixed, otherwise I would have created a table.
– Jonas Centenaro
Why not have a table with all the possibilities and at the time of the query just touch the filter? I find it much more practical than having to create a temporary and popular table every time you consult
– JMSlasher
I called the alias 'letter' but they may be words or phrases, that is, it would not necessarily be a set of only 26 letters of the alphabet would be an infinite set of possibilities, if for each sql I need to make an Insert in a physical table, in the filters of this table it would contain exactly the same as it was in Insert, as: Insert into table (letter) values ('possible passphrase 1'), ('possible passphrase 2'); and in sql it would be: select letter from table Where letter = 'possible passphrase 1' or letter = 'possible passphrase 2'
– Jonas Centenaro
or you can delete everything from the table before inserting, then you wouldn’t need the filter... but even if there are infinite possibilities I see no problem in having the table, even because some other time may be that duplicate and you can use a situation that already exists in the table without having to duplicate record.. but of course this td depends on the problem you are trying to solve sometimes it is better to let the application solve the issue instead of the database
– JMSlasher
Does the VALUES clause not comply? https://www.postgresql.org/docs/current/queries-values.html
– anonimo
I understand the problem. But I need a broader scenario. Is this "LETTER" information something you already have in some table in your database? Because I imagine this would not be the true information. The downside of that doesn’t give you much to understand your current structure to help you solve.
– Ale Suzuki
The expression:
VALUES ('a', 'b', 'c') AS tab_temp(letra)will respond to what you have exposed.– anonimo